Problems with Join Laravel ORM

Asked

Viewed 45 times

2

Hello,

I’m trying to create an advanced query, but I’m having some problems following the code for analysis!

Anuncio::join('users', function ($users) use ($request){
        $users->on('users.id' , '=', 'a.user');
    })->join('enderecos', function ($enderecos) use ($request){
        $enderecos->on('enderecos.id', '=', 'users.endereco');
    })->where('enderecos.uf_id', '=', $request['uf_id'])->get();

The above query works perfectly, but when I try to adapt it to another group of filters it does not work and error!

Follow the other filter group:

$anuncios = Anuncio::where(function($query) use ($request)
    {
        if(isset($request['uf_id'])){
            $query->join('users', function ($users) use ($request){
                $users->on('users.id' , '=', 'a.user');
            })->join('enderecos', function ($enderecos) use ($request){
                $enderecos->on('enderecos.id', '=', 'users.endereco');
            })->where('enderecos.uf_id', '=', $request['uf_id']);
        }
        if(isset($request['modelo'])){
            $query->where('modelo', $request['modelo']);
        }
        if(isset($request['marca']))
            $query->where('marca', $request['marca']);
    })->with('anuncio_dados')->with('adicionais')
        ->with('anuncio_imagens')->with('versaos')
        ->with('marcas')->with('modelos')->paginate(10);

When I try to insert it into this query, the uf_id filter does not work. Can anyone tell me why?

mysql laravel orm
  • 1

    First thing the joins can be simplified already since you don’t use $request or $users ( and others) inside can only be the passage of values. Another point: quando tento inserir ele nesta query o filtro de uf_id não funciona. alguem sabe me dizer o por que? because, error appears what?

  • the error that gives this message: "SQLSTATE[42S22]: Column not found: 1054 Unknown column 'addressees.uf_id' in 'Where clause' (SQL: select Count(*) as Aggregate from anuncios as a Where (enderecos.uf_id = 15))"

  • 1

    There is the column uf_id within addresses?

  • yes, and as I mentioned in the post what left me not knowing what to do, is that in the first query the return is valid!

  • 1

    I found the problem, you can’t do Where with join ... you need to do everything separately, always using the latest version of the collected Builder!

  • Gratitude for your help, but how would that be? I was doubtful how to do!

Show 1 more comment

2 answers

1


Basically the code below solves, the observation is that within where can’t make a join always separate using the latest version of the builder making the criticisms and passing the new value to the same variable, example:

$builderAnuncions = Anuncio::join('users', 'users.id', '=', 'anuncios.user')
                        ->join('enderecos', 'enderecos.id','=','users.endereco');

if(isset($request['uf_id']))
{
    $builderAnuncions = $builderAnuncions->where('enderecos.uf_id', '=', $request['uf_id']);
}
if(isset($request['modelo']))
{
    $builderAnuncions = $builderAnuncions->where('modelo', $request['modelo']);
}
if(isset($request['marca']))
{
    $builderAnuncions = $builderAnuncions->where('marca', $request['marca']);
}    

$builderAnuncions
    ->with('anuncio_dados')
    ->with('adicionais')
    ->with('anuncio_imagens')->with('versaos')
    ->with('marcas')
    ->with('modelos')
    ->paginate(10);

1

From what I understand, you are setting up the query incorrectly. Try following the example below where I select ads with brand name starting with the letter F and including the vehicle brand in the result.

$query = Anuncio::join('marcas', 'marcas.id', '=', 'anuncio.marca_id')->where('marcas.nome', 'like', 'F%')->select(['anuncios.*', 'marcas.nome']);

  • Thanks for the help Junior, I managed to solve with the above example of Virgilio Novic!

Browser other questions tagged mysql laravel orm

You are not signed in. Login or sign up in order to post.