Column Not Found With Inner Join

Question

Lines:

1 - Data from filter form
2 - That with i enter a Model relationship function. I do this because I need to sort my list by column order MEDIA.
3 - The strange thing is that I need to do an INNER JOIN for the same table because if not some columns I want to pick up in QUERY does not work. What I don’t understand about Laravel. Yes, I’m the one who’s wrong, but I don’t know where.
4 - Within this JOIN I seek dealerships that have ratings and that do not have, as I choose in the filter form, so I do this IF.
5 - Then I do the AVG(RATING) to bring the average ranking that the dealership has.
6 - The function ORDER_AVG and the CLOSURE are in the MODEL.
7 - In the filter form there is the option for the user to choose the records that have a certain average (1 to 5). That’s why I’m using the HAVING. But it doesn’t work, it doesn’t filter. I don’t understand why either.

$arrData        = Input::all();

$consulta       = Dealer::with(['order_avg'])
                ->join('dealer_ratings', function($q) use ($arrData){
                    if(array_key_exists('filterByAvaliacao', $arrData)){
                        if($arrData['filterByAvaliacao'] == 1)
                            $q->on('dealer_ratings.id_concessionaria', '=', 'dealers.id');
                        else
                            $q->on('dealer_ratings.id_concessionaria', '<>', 'dealers.id');
                    }
                    else
                        $q->on('dealer_ratings.id_concessionaria', '<>', 'dealers.id');

                    $q->whereNotIn('id_status', [1, 4]);
                })
                ->selectRaw('*, dealers.id, count(dealer_ratings.id) as qtd_avaliacoes, AVG(rating) as media')
                ->groupBy('dealers.id')
                ->orderBy('media', 'desc')
                ->whereIdCidade($arrData['filterByCidade'])
                ->whereIdTipo($arrData['filterByTipo'])
                ->closure(function($query) use ($arrData){
                    if($arrData['filterByMarca'] && $arrData['filterByMarca'] != 0){
                        $query->whereIdMarca($arrData['filterByMarca']);
                    }

                    if($arrData['palavras-chaves'] != ''){
                        $query->where('concessionaria', 'REGEXP', $arrData['palavras-chaves']);
                    }
                })
                ->paginate(10);

MODEL

public function scopeClosure($query, \Closure $callback) {
   $callback($query);
   return $query;
}

# Order By Media de Stars
public function order_avg(){
    return $this->hasMany('App\DealerRating', 'id_concessionaria')
    ->selectRaw('AVG(rating) as media')
    ->having('media', '=', Input::get('filterByStars'));
}

Summary:

I want to pick up a list of dealership conditioned by City and Brand. And in the same query bring the average ranking and ordered by it.

Answer 1

When we use Query Builder we have several ways to encode and when I realize that the code will require many lines, I separate by lines so that I can debug each line.

Example:

$arrData = Input::all();

$consulta = Dealer::with(['order_avg']);
$consulta = $consulta->join('dealer_ratings', function($q) use ($arrData){
              //code
             });

$consulta = $consulta->selectRaw('*, dealers.id, 
             count(dealer_ratings.id) as qtd_avaliacoes, 
             AVG(rating) as media');

And so on in the same footprint as your code. In this way I debug item by item when the generation happens not to work. I also use shortcuts (Scope) and unused Where nominees (in the world Laravel is condemned this kind of nomenclature, example whereId(1) which is the same thing as where('id','=',1), even this coexisting in the Laravel framework).

At this point I am more observing the code, but, as far as I could notice has coding logic errors. As reported in another answer the with is being used as join, but they are completely different things. In the order_avg make a simple Join, the result will start to clear and use the form that shows you from row to row by making a command per line returning to the same variable, thus always having the last Query Builder.

Also debug your SQL as described and shown in this link: Debugging Queries in Laravel:

Example:

$results = User::where(function($q) use ($request) {
    $q->orWhere('email', 'like', '%[email protected]%');
    $q->orWhere('first_name', 'like', '%John%');
    $q->orWhere('last_name', 'like', '%Doe%');
})->toSql();
dd($results);

Code referring link: https://scotch.io/tutorials/debugging-queries-in-laravel

Upshot:

select * from `users` where (`email` like ? or `first_name` like ? or `last_name` like

When the dd it will show the pure SQL being generated by the framework.

Answer 2

The method with Laravel’s sole and exclusive purpose is to bring results related to relationships.

In the context you are using the function order_avgseems to me much more like a Query Scope than a desire to load the relationships.

Query Scope aims to add a chunk of a query, which you will use a lot, to be reused.

For example, if I need to make a same complex Join for 30 queries, I will not give CTRL+C in the method (even because in OOP we have "reuse", and reuse is not CTRL+C + CTRL+V).

Then, to avoid these 30 repeated queries, Query Scope is used:

Example:

  public function scopeAtivos($query)
  {
     $query->whereNotNull('data_ativacao')
           ->whereStatus(1)
           ->where('nivel_id', '<>', 1);
  }

Hence, instead of copying and pasting this query every time, it’s smarter to always do so:

Usuario::ativos()->get();