Column Is Not Found With Inner Join

Question

Column Is Not Found With Inner Join

Navigation

#1 by (1 votes)
#2 by (1 votes)

0

Lines:

1 - Data coming from the filter form
2 - This with I enter into a Model relationship function. I do this because I need to sort my list in order of the MEDIA column. Home 3 - The strange thing is that I need to do an INNER JOIN for the same table, because otherwise some columns I want to get in QUERY do not work. This I do not understand in Laravel. Yes, I am the one who is wrong, but I do not know where. Home 4 - Within this JOIN I look for dealers that have ratings and do not have, as I choose in the filters form, so I do this IF . Home 5 - Then I do the AVG (RATING) to bring the average rating that the dealership has. Home 6 - The function ORDER_AVG and CLOSURE are in MODEL . Home 7 - In the filters form there is the option of the user to choose the records that have a certain average (1 to 5). That's why I'm using HAVING . But it does not work, it does not filter. I do not 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 get a dealer list conditioned by City and Brand. And in the same query bring the average rating and ordered by it.

mysql laravel

asked by anonymous 24.05.2016 / 16:54

2 answers

1

When we use Query Builder we have several ways to code and when I realize that the code will require many lines, I separate by lines so 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 to item when generation happens to fail. I also use shortcuts ( scope ) and unused where named (in the world Laravel is condemned this type of nomenclature, example whereId(1) which is the same as where('id','=',1) , even coexisting in the Laravel framework).

At that point I'm more observing the code, but as far as I can see there are coding logic errors. As reported in another answer the with is being used as join , but, are completely different things. In order_avg make a simple join, the result will start to lighten and use the form that shows you line by line making one command per line returning to the same variable, thus always having the last Query Builder.

Also debug your SQL as described and shown at 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);

Referral code: link

Result:

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

As long as it runs dd it will show the pure SQL being generated by the framework.

24.05.2016 / 17:55

Call page xhtml by commandButton Changing CSS with Jquery

score 1 · Accepted Answer

Laravel's with method has the sole and exclusive purpose of bringing results relating to relationships.

In the context that you are using the order_avg function, it seems to me much more of a Query Scope than a willingness to load relationships .

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

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

So to avoid these 30 repeated queries, use Query Scope:

Example:

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

Then, instead of copying and pasting this query every time, it's smarter to do it like this:

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