[5.4] Model->where loses param if used with withCount(...) and ->select(..)

[masterix21]

• Laravel Version: 5.4.23
• PHP Version: 7.1.3
• Database Driver & Version: MySQL 5.7.16 (community)

Description:

I'm trying to count all customer's contacts (CustomerContacts model) with phone number, but it fails because the model (Customer) loses relationship with company_id (Company model) if you use it with method withCount(..). See the example below.

Steps To Reproduce:

$customers = \App\Customer::where('company_id', '=', 4)->withCount(['contacts' => function($query) { $query->where('phone_no', '!=', ''); }])->select(\DB::raw('customers.*'))->paginate(5);

Query log:

select count() as aggregate from customers where company_id = '4';
select customers. from customers where company_id = '' limit 5 offset 0;

[sausin]

Since you're using the withCount, not sure why you have the select statement in addition. By default you will only the get columns for customers with the additional contacts_count.

The above works for me without the select (though I used get instead of paginate, but I don't think that should affect anything)

[Dylan-DPC-zz]

Kindly close this issue

[themsaid]

Copying @mcbanderson

• Laravel Version: 5.4.33
• PHP Version: 7.0.22
• Database Driver & Version: MySQL 5.6.27

Description:

I recently upgraded my application to 5.4 from 5.2, and as soon as I finished upgrading I started running into this issue. I have 2 custom models, Event and Gift, which have a one to many relationship set up. Before the upgrade everything worked as expected, but after the upgrade any queries that includes ->withCount('gifts') are not working properly.

Some queries, such as the ones below, return an empty Eloquent Collection or null respectively (empty collection for ->get(), null for find(74)). For this example an id of 74 is used, which has been confirmed to exist in the database.

Event::withCount('gifts')->where('id', 74)->get()
Event::withCount('gifts')->find(74)

Other queries, such as the one below, will produce a non-empty collection, but some of the where clauses appear to be completely ignored. In this example the correct gifts_count is returned with each Event, but Events that are older than 2 days appear in the collection (the where clause appears to be completely ignored).

Event::withCount('gifts')->whereIn('user_id', [5, 16, 18])->where('celebration_date', '>=', Carbon::now()->subDays(2))->limit(50)->get()

The relationship appears to be setup correctly, because the query Event::find(74)->gifts()->get() will return an Eloquent Collection that contains all Gifts associated with that Event.

The things that I find very strange are that:

1. Everything was working as expected before the upgrade, and no changes were made to the models.
2. withCount is working for another model, Comment, that has the same relationship to Event as Gift (one Event has many Comments/Gifts).
3. The SQL generated when using ->toSql() on the faulty queries is correct. I have run the generated queries directly in the database and the correct results are returned.

Steps To Reproduce:

I am unsure if this issue is reproducible in a stock installation of Laravel 5.4. I'm hoping to avoid having to setup a clean installation of Laravel 5.4 and manually port my application over, but if I'm unable to resolve this problem in the next day or two I think that will be my next course of action. If there is any additional information I can provide please let me know, and any guidance on possible solutions to try would be greatly appreciated.

[mcbanderson]

@masterix21 Were you ever able to find a solution to this issue?

[mcbanderson]

I manually ported my application to a fresh installation of Laravel 5.4, but the issue persists. If anyone has suggestions on what I could try in order to ferret out the cause of the issue, I would love to hear them. In the mean time I think I will need to revert back to an older version of Laravel so I can avoid this issue.

[mcbanderson]

I reverted to Laravel 5.3.31, which resolved the issue. After reverting to my old commit, I tested the problem queries before and after running composer update in an effort to confirm that the error was not somewhere in my code. The queries were still broken before composer update, but worked after, so I can say with a pretty high degree of confidence that the issue lies somewhere in the Laravel framework or one of its related packages.

[staudenmeir]

Is this still an issue?

[driesvints]

Closing this issue because it's already solved, old or not relevant anymore. Feel free to reply if you're still experiencing this issue.