Apply select join in Laravel ::query() builder-CodePudding

I need to apply the same join i do in this code, but in another code i build it with Eloquent\Builder $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 (\Illuminate\Database\Eloquent\Builder $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!

CodePudding user response：

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

$afiliates = AdAfiliado::query()
    ->where('Activo','=', 'S')                    
    ->where(function (\Illuminate\Database\Eloquent\Builder $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);

CodePudding user response：

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 \Illuminate\Database\Eloquent\Builder

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);