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