Query with multiple join : what's wrong with my query ?

Thank you for your answer.

The index 1648 is in my database, here for the example I have used 1.

Well ... even if I order by sources.name and not only name, I have only the two sources with an amount and the last source without any amount is not in the results.

Do you have any idea ?

Waouuhhhhhhh it's great !!!! IT WORKS !!!!

Thank you very much @michaloravec !!!!

Could you explain me your answer, I want to understand how it works ?

In your case where clause is evaluated after left join, which filter your null results from left join.

Ok thank you ;).

I enjoy ... so I have one other question please.

In my fundings table I have one column more : funding_type which is not in the databasen it's only constants.

What I'd like to have is something like this.

Trainings
- 1, training 1

Sources
- 1, source 1
- 2, source 2
- 3, source 3

Fundings
- 1, 1, 1, 50		// 1, 1, this 1 is the funding_type constant
- 1, 3, 1, 60
- 1, 1, 2, 150
- 1, 3, 2, 250

And the result could be.

source 1, 50, 150
source 2, null
source 3, 60, 250

For the moment I have two queries (one for each funding_type) and I have a loop to feed an array.

But it's perhaps possible to extract the right informations in one unique query ?

$financements = Source::selectRaw('sources.name, SUM(IF(fundings.funding_type = 1, fundings.amount, null)) as amount, SUM(IF(fundings.funding_type = 2, fundings.amount, null)) as amount_two')
    ->leftJoin('fundings', function ($join) {
        $join->on('sources.id', '=', 'fundings.source_id')->where('fundings.training_id', 1);
    })
    ->groupBy('sources.name')
    ->orderBy('sources.name')
    ->get();

http://sqlfiddle.com/#!9/638c68/1

Thank you, I learn several things with you.

I didn't understand why you have used sum() in the query, so I tried without the sum, but it works only with the sum.

Why ? I don't want to sum the values.

I have had to set the mysql strict mode to false in order to have the group by to work.

Is it another way to allow the group by without setting the strict mode to false ?

You can do something like this, but don't do that if you don't have to, in your case use SUM, it works

use Illuminate\Support\Facades\DB; // import facade

config(['database.connections.mysql.strict' => false]);

DB::reconnect();

// query here

config(['database.connections.mysql.strict' => true]);

DB::reconnect();

Yes ok it's effectively better not to set strict mode to false.

I didn't know this use of sum.