MySQL – SQL Join with Laravel Query Builder

MySQL

I have a transactions table with the following field:

Transactions
ID | amount | branch_id | supplier_id 
1  | 1,200  |    2      |  1

Subsidiary
ID  | Name      | Type
1   | supplier1 | 0
2   | branch_2  | 1

I want to display this in my view with the id value properly so I joined the following table (I'm using laravel query builder)

$transactions = DB::table('transactions_table')
->leftJoin('subsidiary_table','transactions_table.supplier_id','=','subsidiary_table.id')
->get(array('transactions_table.id',
'transactions_table.amount',
'subsidiary_table.name as branch',
'subsidiary_table.name as supplier'));

I'm confused on how can I get it to display like:

Display View
ID  |   Ammount  |  Supplier  | Branch 
1   |   1,200    |  Supplier1 | branch_2

Thanks for clarifying..

Best Answer

I don't know if you want to put it in laravel, but, here's the query:

SELECT
 t.ID,
 t.amount,
 s.Name as Supplier,
 sb.Name as Branch
FROM Transactions as t
JOIN Subsidiary as s ON (s.ID=t.supplier_id)
JOIN Subsidiary as sb ON (sb.ID=t.branch_id)
WHERE t.ID = 1;

Try it.

PD: You can change the JOIN syntax for LEFT JOIN if you want to return the t.ID = 1 record from Transactions even if one column don't match in Subsidiary's JOINs.

Related Solutions

Laravel MySQL – Resolving Relation Difficulties

What you expected to have actually exists in Eloquent. In order to attach/detach related models you need to call attach/detach methods on the relations and pass a single ID or array of IDs:

$user->roles()->attach($roleId);
$user->roles()->attach([$roleId1, $roleId2]);
$role->users()->attach($userId);
$role->users()->attach([$userId1, $userId2]);

See some more info at http://laravel.com/docs/5.1/eloquent-relationships#inserting-re lated-models - the examples are pretty close to your case, as it's also about user-role many-to-many relationship.

MySQL Performance – Troubleshooting Slow SQL Query with LEFT JOIN

As a general rule, run (and post the results here) an EXPLAIN on your query to see if indexes are being used or not. At first, I see there are no other indexes on the sessions table except PKs, this will almost surely make your query run slow, since the WHERE will cause the RDBMS do a full table scan to find the rows.
I guess your query will at least require an index on latest_renewal (but, again, check with EXPLAIN).

This is my understanding of your query, on which I based my simplification:

SELECT sessions.member_id
    ,sessions.session_token
    ,sessions.username
    ,sessions.ip_address
FROM bot_sessions AS sessions
LEFT JOIN ( /* */
    SELECT stats.member_id
        ,stats.session_token
        ,stats.username
        ,stats.ip_address
    FROM bot_sessions_statistics AS stats
    WHERE date_active >= DATE (NOW()) /* a. I think this gives all of 'today' statistics */
    ) AS stats ON sessions.member_id = stats.member_id /* b. you're joining on 'member_id' which is NOT NULL in sessions, so any row from this subquery will have a value for (stats.)member_id */
WHERE latest_renewal >= DATE (NOW())
    AND stats.member_id IS NULL /* c. this will filter out all the rows from the JOIN, since no stats.member_id can be NULL */

Next, at first sight (if I'm not mistaken) I think your SELECT query can be rewritten without the LEFT JOIN, like this:

SELECT sessions.member_id, sessions.session_token, sessions.username, sessions.ip_address
FROM bot_sessions AS sessions
WHERE 
    NOT EXISTS (
        SELECT 1 FROM bot_sessions_statistics
        WHERE date_active >= date(NOW())
    )
AND latest_renewal >= date(NOW())

If the above is really correct, compare the results of EXPLAIN on both.

Best Answer

Related Solutions

Laravel MySQL – Resolving Relation Difficulties

MySQL Performance – Troubleshooting Slow SQL Query with LEFT JOIN

Related Question