Laravel How to join 3 user tables while doing a where query

i need to join 3 tables, Expenses, Couples, Users and perform where and orWhere query on expenses. Below is the work flow:

User signs up - user stores their firstname and lastname (column names - firstname and lastname)
User invites their partner - by providing their partner's firstname, lastname (column names - firstname and lastname) and email. The information is stored in the couples table mapped to the user id as the first_partner_id
Partner signs up - and in the couples table partner's id is added as the second_partner_id to the row mapped to first_partner_id.

Now I need to join three tables. But when I do that , the values of firstname and lastname stored in the couples table are overwritten by the values of firstname and lastname in the user table for the first partner. Is there a way to give unique names to firstname and lastname values form the user table while doing a join so that it doesn't override the ones from couple. Theoretically I can rename the columns but that will require code changes at a lot of places.

KalimeroMK

4 years ago

Level 41

need to use alias and select all rows for witch you want to select data

SELECT
     a.client_id AS ClientId
    ,a.NAME AS NAME
    ,a.address_id AS AddressId
    ,a.contact_id AS ContactId
    ,b.address_line_1 AS AddressLine1
    ,c.email AS Email
    ,c.mobile_number AS MobileNumber
    ,c.telephone_number AS TelephoneNumber
    ,c.contact_first_name AS ContactFirstName
FROM
    client AS a
    INNER JOIN address AS b
        ON (a.address_id = b.address_id)
    INNER JOIN contact AS c
        ON (a.contact_id = c.contact_id)
 WHERE condition
;

1 like

singh

4 years ago

Best Answer

Level 1

This is what worked

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

Please or to participate in this conversation.