Eloquent with does not Inner Join?

Question

I remember in the old days "discovering" that in a query using Eloquent, if I used the with Laravel made a inner join.

Today by chance I went to check the queries of a project and...

[2014-11-20 23:21:16] sql.INFO: select * from `ocurrences` where `ocurrences`.`deleted_at` is null order by RAND() limit 4 {"bindings":[],"time":3.58,"name":"mysql"} []
[2014-11-20 23:21:16] sql.INFO: select * from `users` where `users`.`id` in ('7') {"bindings":["7"],"time":0.49,"name":"mysql"} []
[2014-11-20 23:21:16] sql.INFO: select * from `users` where `users`.`id` = '7' limit 1 {"bindings":["7"],"time":0.51,"name":"mysql"} []
[2014-11-20 23:21:16] sql.INFO: select * from `tags` limit 5 {"bindings":[],"time":0.41,"name":"mysql"} []

In this case, I’m query this way:

/**
 * Get random ocurrences for home
 * @return mixed
 */
public static function randomForHome()
{
  return static::with('user')
    ->orderByRaw('RAND()')
    ->limit(4)
    ->get();
}

What’s wrong and/or how I do it with the Eloquent joins?

Answer 1

I had to use the method join() of Eloquent. Initially I thought that the with performed joins as well, but apparently no longer.

So I had to change the with for: join('users', 'users.id', '=', 'ocurrences.user_id').

Anyway, this is it.

Follow the final solution below:

/**
 * Get random ocurrences for home
 * @return Eloquent
 */
public static function randomForHome()
{
  return static::join('users', 'users.id', '=', 'ocurrences.user_id')
    ->orderByRaw('RAND()')
    ->limit(4)
    ->get();
}

Answer 2

It can also be done like this.

DB::table('TABELA')
->join('TABELA2', 'CODIGO.TABELA1', '=', 'CODIGO.TABELA2')
->where('1 = 1')
->get();

Answer 3

Everyone has this doubt regarding the "why not use John" of eloquent, well, I’m not so much on the inside either... but I really believe that it is because of some performance or ambiguity.

By using with it will run the parent query and run a second query to retrieve all children from relationships while maintaining the integrity.

$pedidos = $pedidos
    ->select('id', 'id_pedido', 'hora_pedido', 'local_venda', 'cidade', 'estado')
    ->with(['itens' => function($q){
            $q->where('clientes_id', \Session::get('idClienteTray'));
            $q->select('PedidosTray_id', 'id_produto', 'quantidade');

        },
        'itens.produto' => function($q)
        {
            $q->where('clientes_id', \Session::get('idClienteTray'));
            $q->select('id_produto', 'descricao');

        }])

This is the known method Eager loading

Here’s a friend who gives a possible explanation: https://stackoverflow.com/questions/23920540/why-cant-laravel-eloquent-use-join-for-eager-loading