mardi 25 septembre 2018

Why error with scope when joining 3 tables

In my laravel 5.7 application I use next request based on 3 tables : vote_item_users_result, vote_items, votes

        $voteItemUsersResultsCorrect = VoteItemUsersResult::
            getByIsCorrect(true)->                               // vote_item_users_result   table
            getByCreatedAt($filter_voted_at_from, ' > ')->         // vote_item_users_result   table
            getByCreatedAt($filter_voted_at_till, '< ')->        // vote_item_users_result   table
            getByUserId($filter_user_id)->                       // vote_item_users_result table
            getByVote($filter_vote_id)->                        // vote_items table
//            getByIsQuiz(true)->                              // votes table - COMMENTED
            select( \DB::raw(' DATE_FORMAT(vote_item_users_result.created_at, "%Y-%m-%d") as formatted_created_at , count(vote_item_users_result.id) as count') )->
            orderBy('formatted_created_at', 'asc')->
            groupBy( 'formatted_created_at' )->
            join(\DB::raw('vote_items as vi '), \DB::raw('vi.id'), '=', \DB::raw('vote_item_users_result.vote_item_id'))->
            join(\DB::raw('votes as v '), \DB::raw('v.id'), '=', \DB::raw('vi.vote_id'))->
            get();

it works ok and next sql is generated:

   SELECT  DATE_FORMAT(vote_item_users_result.created_at, "%Y-%m-%d")     AS formatted_created_at , count(vote_item_users_result.id)     AS count 
    FROM `vote_item_users_result` 
    INNER JOIN vote_items     AS vi  on vi.id = vote_item_users_result.vote_item_id 
    INNER JOIN votes     AS v  on v.id = vi.vote_id 
    WHERE `vote_item_users_result`.`is_correct` = '1'     AND vote_item_users_result.created_at  > '2018-09-12'      AND vote_item_users_result.created_at < '2018-09-15'      AND `vi`.`vote_id` in ('4', '3', '2', '5') 
    GROUP BY `formatted_created_at` 
    ORDER BY `formatted_created_at` asc 

The problem is that I want to add 1 more condition :

votes.is_quiz = 1

and for this I uncomment 1 line in my request and got error:

Call to undefined method Illuminate\Database\Eloquent\Builder::getByIsQuiz()

3 modeles defined as :

app/VoteItemUsersResult.php :

<?php

namespace App;
use DB;
use App\MyAppModel;
use App\VoteItem;
use App\Vote;
use App\User;


class VoteItemUsersResult extends MyAppModel
{
    protected $table = 'vote_item_users_result';
    protected $primaryKey = 'id';
    public $timestamps = false;

    protected $fillable = [ 'vote_item_id', 'user_id', 'is_correct', 'created_at' ];

    public function getTableName() : string
    {
        return $this->table;
    }

    public function getPrimaryKey() : string
    {
        return $this->primaryKey;
    }

    public function user()
    {
        return $this->belongsTo('App\User');
    }

    public function voteItem()
    {
        return $this->belongsTo('App\VoteItem');
    }


    public function scopeGetByCreatedAt($query, $filter_voted_at_from= null, string $sign= null)
    {
        if (!empty($filter_voted_at_from)) {
            if (!empty($sign)) {
                $query->whereRaw(with(new VoteItemUsersResult)->getTableName().'.created_at ' . $sign . "'".$filter_voted_at_from."' ");
            } else {
                $query->where(with(new VoteItemUsersResult)->getTableName().'.filter_voted_at_from', $filter_voted_at_from);
            }
        }
        return $query;
    }

    public function scopeGetByUserId($query, $filter_user_id= null)
    {
        if (!empty($filter_user_id)) {
            if ( is_array($filter_user_id) ) {
                $query->whereIn(with(new VoteItemUsersResult)->getTableName().'.user_id', $filter_user_id);
            } else {
                $query->where(with(new VoteItemUsersResult)->getTableName().'.user_id', $filter_user_id);
            }
        }
        return $query;
    }
    ...

app/VoteItem.php :

<?php

namespace App;
use DB;
use App\MyAppModel;
use Illuminate\Support\Facades\File;
use Illuminate\Support\Facades\Storage;
use App\library\ImagePreviewSize;
use Illuminate\Validation\Rule;


class VoteItem extends MyAppModel
{
    use funcsTrait;
    protected $table = 'vote_items';
    protected $primaryKey = 'id';
    public $timestamps = false;

    protected $fillable = [ 'name', 'ordering', 'vote_id' , 'is_correct', 'image' ];

    public function getTableName() : string
    {
        return $this->table;
    }

    public function getPrimaryKey() : string
    {
        return $this->primaryKey;
    }


    public function vote()
    {
        return $this->belongsTo('App\Vote');
    }

    public function voteItemUsersResults()
    {
        return $this->hasMany('App\VoteItemUsersResult');
    }

    public function scopeGetByVote($query, $vote_id= null)
    {
        if (!empty($vote_id)) {
            if ( is_array($vote_id) ) {
                $query->whereIn(with(new VoteItem)->getTableName().'.vote_id', $vote_id);
            } else {
                $query->where(with(new VoteItem)->getTableName().'.vote_id', $vote_id);
            }
        }
        return $query;
    }

and app/Vote.php :

<?php

namespace App;

use DB;
use App\MyAppModel;
use Illuminate\Support\Facades\File;
use Illuminate\Support\Facades\Storage;
use App\Http\Traits\funcsTrait;
use App\library\ImagePreviewSize;
use Cviebrock\EloquentSluggable\Sluggable;
use Illuminate\Validation\Rule;

class Vote extends MyAppModel
{
    use funcsTrait;

    protected $table = 'votes';
    protected $primaryKey = 'id';
    public $timestamps = false;


    protected $fillable = ['name', 'slug', 'description', 'creator_id', 'vote_category_id', 'is_quiz', 'status', 'image'];


    public function getTableName(): string
    {
        return $this->table;
    }

    public function getPrimaryKey(): string
    {
        return $this->primaryKey;
    }



    public function voteCategory()
    {
        return $this->belongsTo('App\VoteCategory');
    }

    public function voteItems()
    {
        return $this->hasMany('App\VoteItem');
    }

    public function scopeGetByVoteCategory($query, $vote_id = null)
    {
        if (empty($vote_id)) {
            return $query;
        }

        return $query->where(with(new Vote)->getTableName().'.vote_category_id', $vote_id);
    }

    public function scopeGetByStatus($query, $status = null)
    {
        if (empty($status)) {
            return $query;
        }
        return $query->where(with(new Vote)->getTableName().'.status', $status);
    }

    public function scopeGetByIsQuiz($query, $is_quiz = null)
    {
        if ( ! isset($is_quiz)) {
            return $query;
        }
        return $query->where(with(new Vote)->getTableName().'.is_quiz', $is_quiz);
    }

I do not see why last calling last scope scopeGetByIsQuiz raise error, as I use several scopes in this request?

Which is the right way ?

Thanks!



via Chebli Mohamed

Aucun commentaire:

Enregistrer un commentaire