Thanks for the information and advice, all. I had the impression that truncating the table would take a while, but from what you've said I was wrong about that. Truncate does sound like the way to go.

Long story short: I maintain a large Laravel database app that runs mainly on PHP, with MariaDB as the database engine. One table in this database is a log of changes to another table. I recently discovered that due to a problem of some kind (not yet sure what), the log table had bloated to a frightening size: more than 167 million records, with the vast majority of them being duplicates: the same update actions being logged over and over again. I immediately disabled the logger function, so the log table is not accumulating new records. I've also made some other changes that I believe have fixed the core issue of repetitive updates. However, now I need to clean out all those duplicate log entries.

The simplest answer is to just wipe the log table. It is not using soft deletes. It has no child tables, so database integrity shouldn't be an issue. I'd lose everything in the log, but at this point that's not a problem. However, I've never faced any question like this before. What's the best way to go about wiping a table that has roughly 167 million records in it, without overloading and crashing the server? Can I use TRUNCATE safely, or do I need to whip up a script that runs during off-hours and deletes records from the table in chunks?

You need to nest orWhere conditions in a where condition. Something like this:

$products = Product::where('parent_id', '!=', 0)
	->whereHas('flags', function ($query) use ($messages) {
		$query
			->where('is_valid', true)
			->where(function ($query) use ($messages) {
				foreach ($messages as $message) {
					$query->orWhere('message', 'LIKE', $message);
				}
			});
	})
	->get();

I might separate the message condition to a scope:

// Flag.php
use Illuminate\Database\Eloquent\Attributes\Scope;

#[Scope]
protected function messageLike(Builder $query, ...$messages): void {
	$query->where(function ($query) use ($messages) {
		foreach ($messages as $message) {
			$query->orWhere('name', 'LIKE', $message.'%');
		}
	});
}

// The query becomes a bit cleaner:
$products = Product::where('parent_id', '!=', 0)
	->whereHas('flags', fn($query) => $query
		->where('is_valid', true)
		->messageLike(...$messages)
	)
	->get();

P.S. parent_id should be a nullable column. If you're using 0 to denote no parent, you can't use a foreign key on the column.

Hi Glukinho,

Thanks for the link. Yes, that looks like the same issue I'm seeing. [sigh] You'd think by now I would know to read the bloody manual. Although I wouldn't have thought to look for an issue about querying on that page.

Your solution looks good - thanks also for that - but I think I'm going to try JussiMannisto's first. I like the idea of having the 'messages' subquery in a separate function that I can then call from elsewhere if I need it.

Isn't it your situation described? https://laravel.com/docs/8.x/eloquent-relationships#chaining-orwhere-clauses-after-relationships

How about this?

$products = Product::query()
    ->where('parent_id', '!=', 0)
    ->whereHas('flags', function ($query) use ($messages) {
        $query
            ->where('is_valid', 1)
            ->where(function ($query) use ($messages) {
                foreach ($messages as $message) {
                    $query->orWhere('message', 'LIKE', $message);
                }
            });
    })
    ->get();

I just ran across what looks like a subtle bug in how Eloquent transforms a query into its SQL equivalent. My application uses Laravel 8 (we haven't upgraded for reasons outside my control), so perhaps this is fixed in a newer version, but I'm stuck with what I have.

This app tracks inventory (products) and each product has a list of "flags" associated with it in a one-to-many relationship. We get incoming data feeds from multiple sources every day that require updating our own product records. A daily cron job is supposed to check products for any such updates in certain fields, by making a list of products that have any of the relevant flags and then updating them using the new feed data. This is the code for the query that retrieves the products for checking:

$products = Product::whereHas('flags', function ($query) use ($messages) {
		    foreach ($messages as $message) {
		        $query->orWhere(function ($query) use ($message) {
		        	$query->where('message', 'LIKE', $message);
		            $query->where('is_valid', 1);
		        });
		    }   
      	})->where('parent_id', '!=', 0)->get();

This query looks like it should retrieve "all products that have a non-empty parent id and there exists a flag that matches the product id AND the flag is valid and its message matches any of the listed ones." The whereHas() condition limits it to flags that match any of the products found by the outer query, which is "all products that have a parent id". This will return a strictly limited subset of products. But some server load issues led me to take a close look at it, and this is the query that MariaDB is actually executing:

select * from `products` where exists 
	(select * from `flags` where `products`.`id` = `flags`.`product_id` or 
		((`message` LIKE 'FLAG: [SELF] The attribute \'%\' has changed.' and `is_valid` = 1) 
		or (`message` LIKE 'FLAG: [SELF] The attribute \'%\' is new.' and `is_valid` = 1) 
		or (`message` LIKE 'FLAG: The attribute \'%\' has changed.' and `is_valid` = 1) or 
		(`message` LIKE 'FLAG: This product has been reassigned from master%' and `is_valid` = 1) or 
		(`message` LIKE 'FLAG: The attribute \'image_large\' is new%' and `is_valid` = 1) or 
		(`message` LIKE 'FLAG: The attribute \'image_small\' is new%' and `is_valid` = 1))) 
	and `parent_id` != 0 and `products`.`deleted_at` is null

In English, I read this as "all products that have a non-empty parent id and there exists a flag such that its product id matches the product id OR the flag is valid and its message matches any of the listed ones." That subquery will be true for every product, which means it's checking the whole flags table for every matching product, and the only effective limit on the result set is the parent_id condition. Turns out that's a real cpu-killer when you've got more than half a million such records in the Products table, and several million valid flags in the Flags table.

Why are the two conditions on the 'flags' subquery - (product id matches) and (the "matches message" criteria) - joined by an OR instead of an AND, as I would expect from the Eloquent query code? Can I quickly rewrite this query to do what I want it to, or do I have to resort to something like getting the list of (parent id is not empty) products and then checking those for any of the target flags with a foreach loop?

Well, I didn't change the htdocs location - it's still c:/xampp/htdocs, so I'm not sure how that would make a difference. I looked through all the other config files I know about and couldn't find anything that looked wrong, but that may just mean there's a config file I don't know about.

I did manage to fix one of the 'path not found' calls by removing a leading slash from the route in web.php. However, the same fix didn't work with another call that has been generating the 'path not found' error.

The routes I'm trying to use are in the routes list. If I run the php artisan server, the route redirects work properly and I do not get the error.

Sorry to report that didn't help, Glukinho. I even threw in a clearing of browser cache, to make sure nothing was lingering there. No joy.

Situation: I have a Laravel app with a production version, a staging version, and the development version that I keep on my local hard drive. My dev computer uses XAMPP as its server. Up until a month or so ago, the dev version was in a folder directly under C:, not under xampp/htdocs. I had created a virtual host to redirect "app.test" to the app's root folder, and it all worked fine that way.

Then I reorganized my hard drive, and in the process I moved the dev version to a folder of its own under xampp/htdocs. At the time this didn't seem to make any difference, and it still ran fine. However, while experimenting with a new function today, I discovered that certain routes no longer work. To be exact, any route that gets referenced through an ajax GET call returns a 404 error, even though the route absolutely does exist in web.php, and the code in the web.php file hasn't changed. This problem is limited to my dev version; the routes still work correctly in the staging and production versions. This convinces me that it's a configuration error.

I've checked and confirmed that references to the old virtual host are commented out in the vhosts file. URL rewriting is definitely enabled. The URLs I'm trying to call show up in the routes list. I've shut down and restarted the Xampp server, and even completely rebooted the computer in case something was lingering in RAM. I've checked route definitions, cleared the route cache, checked the syntax of the URLs being called, checked httpd.conf and .htaccess, regenerated the autoload files... everything I can think of. Nothing has helped.

What else could be causing this?

Because I've had it happen in the past that I tried something myself, thought I had it right, then later discovered there was some aspect of it that I didn't know about, which resulted in my code working as intended sometimes and not working sometimes, and figuring out exactly what was going on and how to fix it was hellish. Worse, on at least one occasion I discovered that there was no way to fix it; I had to throw out several days worth of work and start over from scratch.

I'm writing some complex validations for an inventory table - a table of products. This Products table has a couple of related child tables. I'd like to save some resources by only running validations for fields that have changed.

Scenario: I edit Product A, change a couple of fields in the Product record and also change some of the data that's stored in one of the child tables. Next, I edit Product B and only change child-table data; the Product record isn't changed at all.

What will wasChanged() show for Product A? What about for Product B?

A thorough and easy-to-understand answer, jsanwo64. Thanks. I had a feeling it was a precedence issue, but didn't know how to fix it. .

Situation: I have three tables: Product (inventory products), Flags (indicator flags that show 'something needs attention'), and Rules (rules for creating Flags). The relationship chain goes product->flags->rules. Each product can have many flags, while each flag belongs to one rule (but one rule can have many flags).
We're transitioning to a new way of defining and handling flags. Under the old way, the flag record itself contains a flag message, and the first few characters of the message are the severity. Under the new way, the severity is shown by a boolean field in the rule definition: false means low severity, true means high severity.
I need a way to retrieve all flags for a given product where severity = 'high', whether the flag was defined under the old way or the new way. That is, I want a query in a Product class method that says "retrieve all flags for this product where either flag->message like 'HIGH%' or flag->rule->severe = TRUE."
I've tried the straightforward approach:

$flagList = $this->flags()
			->where('message',  'like', 'HIGH%')
			->orWhere('rule->severe',TRUE) 
			->get();

but got an error because table Flags doesn't contain a column called 'rule'. I also tried using an orWhereHas query:

$flagList = $this->flags()
			->where('message', 'like', 'HIGH%')
			->orWhereHas('rule',function($q) {
					$q->where('severe',TRUE);
				})
			->get();

but that didn't work either -- I got all high-severity flags on all products, not just the current one. How do I do this query?

Thanks.

If App 1 is running on Apache and App 2 is running on "php artisan serve", I don't get the error. It's clumsy to set up, but it looks like I can use that as a workaround. Thanks for the idea.

Gotta admit, though, I don't understand that at all. How can the php artisan server even see a database created in XAMPP's MySQL instance??

Let me see...

Config shows significant differences between app 1 and app 2: app 1 has connections to sqlite, mysql, mysql_old, pgsql, and sqlsrv. App 1 also has a redis entry for "cache" as well as the default one. App 2 has database connections for sqlite, mysql, and pgsql, and only a default redis connection. The database names used in both are correct.

There are a few raw queries in app 2, but none in the specific sequence that fires when the API call from App 1 comes in. App 2 doesn't mention app 1's database by name anywhere.

Is this something to do with the redis cache, perhaps?

Update: I figured out what I did wrong to get the blank screen and have fixed it. I still am getting the original error, in which App 2 tries to use App 1's database instead of its own.

There is no DB_NAME defined in either app's .env file. There is a DB_DATABASE, and each env file has the correct database name there.

I noticed that one env file had the DB_HOST as 127.0.0.1 and the other had it as "localhost". I set both to be "localhost" and now the new function in App 1 won't run at all. Won't even acknowledge changes in its own code. No matter what I do, as soon as I try to call this new function, I get a blank screen. The rest of App 1 seems to work fine, but the new bit might as well not be there. I just changed the route:: call to call a function that doesn't exist, cleared browser cache, cleared Laravel cache, and tried calling it -- and still got just a blank screen. No error message of any kind, nothing in the console, just a blank screen.

I have two Laravel apps that I'm trying to get to work together on a local XAMPP server. Specifically, I'm testing a reporting feature on application 1 (a retail-sales-tracking app) that requests some data from application 2 (an inventory-tracking app). In production the two run on different servers, but this is my local test machine so they're both running on the same server, and both databases are accessible from the same MySQL session.

I've written an API request handler in app 2 that can successfully handle the data request when I send it from Postman. However, when I try to call that API path from app 1, it fails because of a "table not found" error. Specifically, the error message in the log says "SQLSTATE[42S02]: Base table or view not found: 1146 Table 'app1.settings' doesn't exist". There is no table called "settings" in App 1 -- but there is one in App 2, and the error is being thrown from App 2.

It looks like something somewhere is not getting reset to recognize the change from App 1 to App 2, so App 2 doesn't know to use "App2" as the database name instead of "App1". Is this correct? If so, how can I explicitly force App 2 to look at its own database in this situation?