Query using max and group

Have you tried with the relationship? Here is the sample code of your idea.

In Template.php-

public function qualFormTemplate(){
    return $this->hasOne('App\QualFormTemplate');
}

In QualFormTemplate.php.

public function template(){
    return $this->hasOne('App\Template');
}

Now you can easily call data from the template model in your controller-

$templates = Template::with(['type', 'qualFormTemplate'])
    ->get();

Note: I haven't tested your code yet, hope it will work.

@tisuchi I already have relationship in place, the question here is how to query only the data that i need, from the example give, i need return filter 6 items and make them 3 items.

Having in my table the following rows: Row........: 1-----2-----3-----4-----5-----6

id_Type...: 1-----1-----1-----2-----2-----2

Variant....: 1-----1-----2-----1-----1-----1

Version...: 1-----2-----1-----1-----2-----3

I want to get back only 3 rows (colums here because i cannot use table in this texteditor)

Row........: 2-----3-----6

id_Type...: 1-----1-----2

Variant....: 1-----2-----1

Version...: 2-----1-----3

The latest variant and version of each type.

@ABNERSOUZA - Sorry. I am bit confused about your question.

You may use screenshot for better visualization.

@tisuchi Here is the actual and intended return from the DB

https://ibb.co/4PXnT0T

If you could help.

Thanks

A join and a group by example is something like

This is just an example:

$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
// more

Example uses count just change to Max or whatever aggregate you need.

Hey @jlrdw this is the final query that i need to convert to use the builder:

SELECT T.*, QFT.approved FROM ( SELECT MAX(id) id, type_id, variant FROM template_templates GROUP BY type_id, variant) AS TT JOIN template_templates T ON TT.id = T.id LEFT JOIN qual_form_templates AS QFT ON TT.id = QFT.template_id;

It will be similar to my example and you have to remember these queries can take a little trial and error to work out just right.

Your aggregate will probably have to be a raw expression.

Just look in the docs under query Builder and Taylor has many examples.

Of course you can use DB facade also and just use your query as is if it works.

@jlrdw so many attempts and haven't get it working yet. Will keep trying.