using paginate with eager loading on a relation with group

@Sinnbeck That's the thing ... the paginate and the with affects in the relation and therefore, the group by

There's no more than that ... I copy you compete.

The SQL generated in the relation without the "with" is:

select * from Model2
where Model2.model1_id = 10513
 group by `viewer_id`

if I put the with(), when I use the relationship to read the model, the SQL is

select * from Model2 
where Model2.model1_id in ( 9743, 9773, 9823, 9851, 9877, 9879, 9880, 9881, 9910, 10055, 10057, 10058, 10131, 10489, 10513 )
 group by `viewer_id`

Those numbers are the id's of the 15 items in that "page".

@skater When you show pseudo code it makes it almost impossible for us to help you.

@Tray2 The only thing I changed is the name of the models ... if you prefer I change it to real names, but it's not pseudocode... it's copy pasted ! :-D

Let me change it !

@skater I'm curious why you are adding a group by on the relationship in the first place. That means you can only select the "field" column and not "model1_id"

@Tray2 Changed it with ALL the full code

@Sinnbeck that groupby is because the Model2 (Reads) table has other field which I want (I Need) to group by ... nothing else ... business model needs ;-)

My problem is that groupby is affecting the results when I use pagination, since the "where" generated because of the "with" eager loading CHANGES radically.

If I do not use the "with" and I use the "relation" in the foreach loop (more queries, obviously), the where clause just points to each specific id ... and that results me in the results I want

If I use the "with" to "eager loading", the where clause is different as shown ... and the groupby misleads everything.

@skater You should not use group by unless you use an aggregate function

This

select * from Reads
where Reads.book_id = 10513
group by `field`

Should be this

select * from Reads
where Reads.book_id = 10513

The same goes here

select * from Reads
where Reads.book_id in ( 9743, 9773, 9823, 9851, 9877, 9879, 9880, 9881, 9910, 10055, 10057, 10058, 10131, 10489, 10513 )
group by `field`

Remove the group by, it doesn't serve any purpose.

@Tray2 I use agreggated ... that groupby is, as I said, important for business model purposes ... I need to take distinct values of "field" of the result

I re-ask the question...

I have a relationship, in which I have a groupby ... and I need it and it has to be like that.

How can I use that relationship in a "with", without this use affects to the results

@skater No you are not using any aggregate function.

You are using an group by without any aggregate function.

https://dev.mysql.com/doc/refman/8.0/en/aggregate-functions.html

You are just selecting everything from the column, if you want to select distinct values you should either filter properly or as a last resort use distinct-

@skater I suggest use one or the other:

• Joins and grouping or

• Related data

And if you need a more detailed tutorial on aggregate usage see: https://www.mysqltutorial.org/mysql-group-by.aspx

Edit:

Related data is like:

Company A
--- List of their payables

Company B
--- List of their payables

etc

Aggregate / grouping is like:

$quy = Powner::query()->leftJoin('dc_pets', 'dc_powners.ownerid', '=', 'dc_pets.ownerid')
                ->select('dc_powners.ownerid', 'dc_powners.oname')
                ->selectRaw('count(dc_pets.petid) as countOfPets')
                ->groupby('dc_powners.ownerid')
                ->orderby('dc_powners.oname')
                ->get();

Results basically give:

ownerid, oname, countOfPets

Like:

5|Bob|3
4|Greg|9
2|Rob|1

Just examples

But in reports you also have section summaries and usually at the end some final totals. These summaries and final totals are separate queries.

Edit:

Seems you could just do something like:

Pseudocode

select the books that user_id 5 read  // just example

Or are you wanting a list of who read what?

Bob

----read book 1
----read book 458

Dave

----read book 1
----read book 900

etc