Question 17158 in Laravel Question posted in
The official Laravel Documentation can be found here.

Apply select join in Laravel ::query() builder

I need to apply the same join i do in this code, but in another code i build it with EloquentBuilder $query

The join I want is this:

$afiliates = DB::table('ad_afiliado as af')
        ->join('af_promocion as promo', 'af.Clave', '=', 'promo.id_afiliado')
        ->select('af.logo_url', 'af.NombreComercial', 'af.Destacado', 'af.id_afiliado', 'af.Clave', 'af.DestacadoInicio')                                            
        ->where('promo.v_fin','>',$FechaActual)            
        ->paginate(9);

The Code where I want to put the join is this:

 $afiliates = AdAfiliado::query()
            ->where('Activo','=', 'S')                    
            ->where(function (IlluminateDatabaseEloquentBuilder $query) use ($request) {
                $query->orWhere('NombreComercial', 'like', "%{$request->search}%");
                $query->orWhere('Etiqueta', 'like', "%{$request->search}%");
                $query->orWhere('Categoria', 'like', "{$request->search}");
            })
            ->orderBy('CLAVE', $request->order)                                                            
            ->paginate(9);

I appreciate your help!

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0 
    I got solved, as I read in this site:

    https://ashallendesign.co.uk/blog/using-query-in-laravel-eloquent-queries

    the query() method is not necesary, so i removed it, and did it in DB::table way.

    I also removed the route IlluminateDatabaseEloquentBuilder

    The resulting code is this:

    $afiliates = DB::table('ad_afiliado as af')
        ->join('af_promocion as promo', 'af.Clave', '=', 'promo.id_afiliado')
        ->select('af.logo_url', 'af.NombreComercial', 'af.Destacado', 'af.id_afiliado', 'af.Clave', 'af.DestacadoInicio')   
        ->where('promo.v_fin','>',$FechaActual)
        ->where('af.Activo','=', 'S')                    
        ->where(function ($query) use ($request) {
            $query->orWhere('af.NombreComercial', 'like', "%{$request->search}%");
            $query->orWhere('af.Etiqueta', 'like', "%{$request->search}%");
            $query->orWhere('af.Categoria', 'like', "{$request->search}");
        })            
        ->orderBy('af.CLAVE', $request->order)                                   
        ->paginate(9);

  2. 0

    You can use whereHas() using the relation between AdAfiliado and AfPromocion

    $afiliates = AdAfiliado::query()
    ->where('Activo','=', 'S')                    
    ->where(function (IlluminateDatabaseEloquentBuilder $query) use ($request) {
        $query->orWhere('NombreComercial', 'like', "%{$request->search}%");
        $query->orWhere('Etiqueta', 'like', "%{$request->search}%");
        $query->orWhere('Categoria', 'like', "{$request->search}");
    })
    ->whereHas('afPromocions', function($afPromocion) use($FechaActual) {
        $adPromocion->where('v_fin', '>', $FechaActual);
    })
    ->orderBy('CLAVE', $request->order)                                                            
    ->paginate(9);
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search