How to use JOIN to get all records even if there's no match in the other table in Laravel's Eloquent?

Web Development
,
1

I am working on a financial management system where I have two tables, “Funds” and “FundingSources”. I want to retrieve information about each fund, including information from the “FundingSources” table, such as the source name, estimated cost, contract value, and party name. However, some funds may not have a corresponding funding source yet, and I still want to display the fund information in these cases.

$funds = DB::table('funds')
->select('funds.id', 'funds.package_code', 'funds.amount', 'funds.method', 'funding_sources.name',
'.funding_sources.estimated_cost', 'funding_sources.contract_value', 'funding_sources.party_name')
->join('funding_sources', 'funding_sources.id', '=', 'funds.funding_source_id')->get();

When I run my code using Laravel’s Eloquent with a JOIN, I only get the records where there is a match in both tables. But I need to display all the records from the “Funds” table even if there is no corresponding record in the “FundingSources” table. How can I fix this problem and get all the records?

2

You can use an outer join. MySQL supports three types of outer joins: LEFT JOIN, RIGHT JOIN, and FULL OUTER JOIN.

A LEFT JOIN returns all records from the left table and the matching records from the right table. The result will have NULL values for the right table if there’s no match.

A RIGHT JOIN returns all records from the right table and the matching records from the left table. If there’s no match, the result will have NULL values for the left table.

A FULL OUTER JOIN returns all records from both tables, including the unmatched ones, with NULL values in the columns where there’s no match.

Here is a revised version of your code.

$funds = DB::table('funds')
->select('funds.id', 'funds.package_code', 'funds.amount', 'funds.method', 'funding_sources.name',
'.funding_sources.estimated_cost', 'funding_sources.contract_value', 'funding_sources.party_name')
->leftJoin('funding_sources', 'funding_sources.id', '=', 'funds.funding_source_id')->get();

Note: The left join and left outer join are the same.

You can also use JOIN and get the same effect.

->join('funding_sources', 'funding_sources.id', '=', 'funds.funding_source_id', 'left outer')

The join method has a fourth parameter called $type, which defaults to “inner” if not specified.