orderLogic breaks CRUD column #1333
-
|
I have two tables - users and dues. I have a Users CRUD that lets you add / remove users and I have a Dues CRUD that doesn't actually let you add / remove dues (that can all be done in the Users CRUD) but that lets you see who has and hasn't paid their dues for the year. The Dues CRUD has the User model as its base model because the user table is the one table I'm always including. Like if someone hasn't paid their dues for a given year they may not have an entry in the dues table, however, they absolutely have an entry in the users table. So for the Unpaid filter that I have I'm using query builder to do a LEFT JOIN on the dues column. To make this work I'm using CRUD::addClause(function ($query) use ($year) {
$query->join('dues as d', function ($join) use ($year) {
$join->on('users.id', '=', 'd.userId')
->where('d.year', '=', $year);
})
->whereNotNull('d.datePaid');
});In the list operation (the only operation I've actually defined) I'm showing the following columns:
The idea with showing the spouse is that if you're paying dues for you and your spouse your annual dues are cheaper than if you're paying for your dues for just yourself. To facilitate this I'm using this CRUD column for Spouse: CRUD::column([
'label' => 'Spouse',
'name' => 'spouse.name',
'type' => 'relationship',
'entity' => 'spouse',
'attribute' => 'name',
'model' => 'App\Models\User',
'orderable' => true,
'orderLogic' => function ($query, $column, $columnDirection) {
return $query->leftJoin('users AS u2', 'u2.spouseId', '=', 'users.id')
->orderBy('u2.name', $columnDirection);
},
'searchLogic' => function ($query, $column, $searchTerm) {
$query->orWhereHas('spouse', function ($q) use ($searchTerm) {
$q->where('name', 'like', '%'.$searchTerm.'%');
});
}
]);And that mostly works well. The problem is when I try to sort by the Spouse column in the CRUD UI. Once I do that the names of all users without spouses show up blank as the following image shows:
This doesn't appear to be a problem with the underlying query. Like if I modify select
users.name,
d.datePaid,
d.amountPaid,
IF(d.paypalID IS NULL, 'No', 'Yes') AS paidOnline,
u2.name
from
`users`
inner join `dues` as `d` on `users`.`id` = `d`.`userId`
and `d`.`year` = 2024
left join `users` as `u2` on `u2`.`spouseId` = `users`.`id`
where
`d`.`datePaid` is not null
and `users`.`deleted_at` is null
order by
`u2`.`name` asc,
`users`.`id` desc(Backpack is actually doing So what's wrong with my orderLogic, since that seems to be what's breaking things? |
Beta Was this translation helpful? Give feedback.
Replies: 3 comments
-
|
The issue you're experiencing with the When you use The solution is to add a 'orderLogic' => function ($query, $column, $columnDirection) {
return $query->leftJoin('users AS u2', 'u2.spouseId', '=', 'users.id')
->orderBy('u2.name', $columnDirection)
->select('users.*'); // Add this line
},This issue has been documented in several places in the Backpack community. When using joins in This is a known pattern in Backpack as mentioned in this GitHub issue, where a similar problem was solved by adding the Another similar issue was reported in this discussion, where the solution was also to add the explicit select statement to ensure the correct columns are returned. By adding the Let me know if this resolves your issue! ℹ️ Please note our AI Bot does not reply to follow-ups, this will be the only AI answer in this thread. If the AI helped you find the answer, please mark it as answered or write details below so it can learn. Then close the conversation. If not, please give more information for when a human reads this. ℹ️ Relevant sources: orderLogic |
Beta Was this translation helpful? Give feedback.
-
|
That worked! That said I guess I'll keep this open vs closing it because this kinda does seem like a bug to me. If one of the project maintainers wants to close they're free to do so! |
Beta Was this translation helpful? Give feedback.
-
|
Hello @prescriptionlifeline, thanks for raising this! I don’t believe this is a bug, because with a left join you end up with two columns named name, and the system may display one that happens to be empty. To ensure the correct data is returned, I recommend making the selection explicit (for example, by aliasing the columns). I’m going to close this issue for now, but please feel free to reopen it or create a new one if you have further questions or run into related problems. Cheers! |
Beta Was this translation helpful? Give feedback.
The issue you're experiencing with the
orderLogicfor your spouse column is a common problem when using joins in Backpack. The problem isn't with your query logic itself, but rather with how Backpack handles the selection of columns when using custom ordering with joins.When you use
orderLogicwith a join, you need to explicitly select the columns from your main table to avoid data being overwritten by the joined table. In your case, when you sort by spouse, the query works correctly, but Backpack isn't properly selecting just the columns from your main users table.The solution is to add a
select('users.*')to yourorderLogicfunction: