One of many relation not working in postgres due to missing group by clause

[w00key]

trafficstars

Firstly, thanks for a great package, I use it as part of a datatables style query helper.

I have a one of many relation between User and Posts, so a user can have many posts and only one latest post.

When using the powerjoin function of

$powerUser = User::joinRelationship('latestPost')->first();

The query fails due to group by issue

Grouping error: 7 ERROR: column "users.id" must appear in the GROUP BY clause or be used in an aggregate function LINE 1: select "users".*, MAX("posts"."id") as "id_aggregate", "post... ^

I've provided a repo to reproduce this, you will just need a postgres database.

https://github.com/w00key/powerjoin-example

The example code is in the web routes for the homepage.

I also grabbed your package and tweaked the test suite to run on postgres by changing the config and added trait RefreshDatabase to the testcase, this highlighted that this test fails for the same reason.

1. Kirschbaum\PowerJoins\Tests\JoinRelationshipTest::test_has_one_of_many

There is a second test that fails but I suspect that's due to them being designed for sqlite which I completely understand why.

[luisdalmolin]

hmm, this one is a bit tricky. Can I ask what is the use case here? e.g. is your intention to just join the relationship as a whole (posts), or to join specifically the latest post in this situation? This is a feature that was added but the use case was never nailed 100%, so may be time to figure out the best solution here

[w00key]

I'm looking to join in the latestPost which is a has one of many (single model per row), posts already works as expected. So as part of our datatable system we'd select say title from latestPost and then we'd be able to filter on that etc.

Our workarounds right now mean filtering, sorting etc is disabled because its not at a query level.

[luisdalmolin]

Yep that makes sense. I'll have to think a bit on this. This actually works on MySQL but I need to think on the best way to solve this for Postgres.