What is Wrong with my Query Builder Join - it won't give me the data I get in raw SQL ?

@vincej You need selectRaw there

    ->selectRaw('contractors.crew_id, count(*) as members, skill_type.skill, location.location_name, crews.crew_name')

@JarekTkaczyk

Jarek - you are truly brilliant !! Thank you !

The syntax I used was copied straight out of the docs. Curious that it does not work.

You might notice that the essential structure of this query is similar to the Team / Players query you helped with on Tues. Only in this case it is Crew / Contractors. It is the same query, just I used Team / Players to make it obvious to the forum what I needed.

Then I realised I needed more data that just members count in the same query: Location, Crew Name, Skills. So I have built off of your example. I am now going to have to go through the same exercise as before: parsing over the array. It should be easier now with a foreach.

Many Thanks Again !! Another award point ! :o)

The syntax I used was copied straight out of the docs. Curious that it does not work.

@vincej can you point exactly where in the docs have you copied it from?

@toniperic

Sure - http://laravel.com/docs/master/queries#joins

cheers !

@vincej all the relevant snippets from the docs I could see (relevant to your code) is the following snippet actually:

$users = DB::table('users')
    ->join('contacts', 'users.id', '=', 'contacts.user_id')
    ->join('orders', 'users.id', '=', 'orders.user_id')
    ->select('users.*', 'contacts.phone', 'orders.price')
    ->get();

Is that the one? If yes, the docs are valid, it's just that you made a small mistake.

When aliasing something (a sql function call) in your query statement (using select count(*) AS total) you should always stick to selectRaw().

For a moment you made me think that the docs were wrong. :)

@toniperic cheers that's brilliant, thank you.

@toniperic @vincej In fact aliasing is handled by the builder/grammar:

select('name as first_name') OK

but

select("concat(first_name, ' ', last_name) as name") wrong

That said, you need selectRaw whenever you want to use sql functions, aggregates etc

@JarekTkaczyk actually yes, spot on.

PS: when's the book coming out?

@toniperic soon... I'm having hard time lately with my 8 legged 2 and a half kids ;)

@JarekTkaczyk

I am sorry to trouble you again especially so late at night.

It does not feel right to have the query sitting in my controller. So following your example from Tues I place it in the model an try to call it:

  $contractorsTest = Crew::with('contractorsTest')->get();

Model:

    public function contractorsTest()
    {
        return $this->hasOne('App\Models\Contractor')
       /* DB::table('contractors')*/
            ->join('auburntree.crews', 'contractors.crew_id', '=', 'crews.id' )
            ->join('auburntree.skill_type', 'contractors.crew_id', '=', 'crews.id')
            ->join('auburntree.location', 'location.id', '=', 'crews.location_id' )
            ->selectRaw('contractors.crew_id, count(*) as members, skill_type.skill, location.location_name, crews.crew_name')
            ->groupBy('contractors.crew_id')
            ->get();
    }

And I get an error:

Call to undefined method Illuminate\Database\Eloquent\Collection::addEagerConstraints()

@JarekTkaczyk

Ok - I fixed it. I saw I had the get()

Thanks again !