inner join and left join in Eloquent Laravel

Question

inner join and left join in Eloquent Laravel

Navigation

#1 by (1 votes)

1

Assuming we have the following table structure in a database

USERS TABLE

MariaDB [blog]> describe users;
+--------------+--------------+------+-----+---------------------+----------------+
| Field        | Type         | Null | Key | Default             | Extra          |
+--------------+--------------+------+-----+---------------------+----------------+
| idUser       | bigint(20)   | NO   | PRI | NULL                | auto_increment |
| nameUser     | varchar(100) | NO   | UNI | NULL                |                |
| passwordUser | varchar(100) | NO   |     | NULL                |                |
| statusUser   | tinyint(1)   | NO   |     | NULL                |                |
| created_at   | datetime     | NO   |     | current_timestamp() |                |
+--------------+--------------+------+-----+---------------------+----------------+

Later the following structure

TABLE CATEGORIES

MariaDB [blog]> describe categories;
+---------------------+--------------+------+-----+---------------------+----------------+
| Field               | Type         | Null | Key | Default             | Extra          |
+---------------------+--------------+------+-----+---------------------+----------------+
| idCategory          | bigint(20)   | NO   | PRI | NULL                | auto_increment |
| nameCategory        | varchar(100) | NO   | UNI | NULL                |                |
| descriptionCategory | mediumtext   | NO   |     | NULL                |                |
| statusCategory      | tinyint(1)   | NO   |     | NULL                |                |
| user_id             | bigint(20)   | NO   | MUL | NULL                |                |
| created_at          | datetime     | NO   |     | current_timestamp() |                |
+---------------------+--------------+------+-----+---------------------+----------------+

By making a query with INNER JOIN or JOIN in mariaDB we should obtain all the records that have a link or relationship between both tables; in other words all the users who in turn have registered at least one category registered of discharge; as follows

MariaDB [blog]> SELECT users.nameUser, categories.nameCategory
    -> FROM users
    -> JOIN categories
    -> ON users.idUser = categories.user_id;
+----------+---------------+
| nameUser | nameCategory  |
+----------+---------------+
| alfa     | backend       |
| beta     | frontend      |
| gama     | servers       |
| delta    | mantenimiento |
+----------+---------------+

For example, for now to obtain the total list of users including those who do not have associated the registration of any category, we use a LEFT JOIN as follows

MariaDB [blog]> SELECT users.nameUser, categories.nameCategory
    -> FROM users
    -> LEFT JOIN categories
    -> ON users.idUser = categories.user_id;
+----------+---------------+
| nameUser | nameCategory  |
+----------+---------------+
| alfa     | backend       |
| beta     | frontend      |
| gama     | servers       |
| delta    | mantenimiento |
| master   | NULL          |
+----------+---------------+

How to pass these two queries to the Eloquent ORM without using necessarily the queryBuilder?

laravel laravel-5

asked by element 17.08.2018 в 03:23

source

1 answer

How to create dynamic inputs with Django? How to get the "name" and not the "id" in jQuery ..? From MYSQL

score 1 · Accepted Answer

Assuming that in our scenario we have the model of the users table called User , through the chaining of methods we can do the following using select() and join() both methods in Eloquent

In the construction of the query, the variable $data equals to the model class User and this in turn accesses the static method select()

INNER JOIN

$data = User::select('users.nameUser', 'categories.nameCategory')
                ->join('categories', 'users.idUser', '=', 'categories.user_id')
                ->get();

        return $data;

The above gives me to return the following information

[
{
"nameUser": "alfa",
"nameCategory": "backend"
},
{
"nameUser": "beta",
"nameCategory": "frontend"
},
{
"nameUser": "gama",
"nameCategory": "servers"
},
{
"nameUser": "delta",
"nameCategory": "mantenimiento"
}
]

LEFT JOIN

$data = User::select('users.nameUser', 'categories.nameCategory')
                ->leftjoin('categories', 'users.idUser', '=', 'categories.user_id')
                ->get();

        return $data;

The above gives me to return the following information

[
{
"nameUser": "alfa",
"nameCategory": "backend"
},
{
"nameUser": "beta",
"nameCategory": "frontend"
},
{
"nameUser": "gama",
"nameCategory": "servers"
},
{
"nameUser": "delta",
"nameCategory": "mantenimiento"
},
{
"nameUser": "master",
"nameCategory": null
}
]