CODEWITHSUNDEEP
phpmysqldatabaselaraveleloquent
Adam Copley
Adam Copley

Reputation: 1495

Exclude join columns from Laravel scope query ONLY_FULL_GROUP_BY error

I have a scope query in a Laravel project which is implicitly fetching two columns which I don't want in the result set, because they are causing an ONLY_FULL_GROUP_BY error, I don't want to disable this database condition.

We have the following relations:

Organisation has -> Categories

public function categories()
{
    return $this->belongsToMany(
        Category::class,
        'organisation_unit_template_categories',
        'organisation_unit_id',
        'template_category_id'
    );
}

Categories has -> Templates

public function templates()
{
    return $this->hasMany(Template::class);
}

Template has -> Dimensions

public function dimensions()
{
    return $this->belongsTo(Dimensions::class, 'dimensions_id');
}

Our categories also have a scope query, so that we can get all categories which contain at least one template who's dimensions have 'digital = 0'

public function scopeIsPrint($query)
{
    return $query
        ->select($this->getTable().'.*')
        ->join('templates', 'template_categories.id', '=', 'templates.category_id')
        ->join('template_dimensions', 'template_dimensions.id', '=', 'templates.dimensions_id')
        ->where('template_dimensions.digital', 0)
        ->groupBy($this->getTable().'.id');
}

We call the scope query from a controller like so:

$categories = $this->organisation->categories()->isPrint()->get();

This is outputting:

SELECT 
    `template_categories`.*,
    `organisation_unit_template_categories`.`organisation_unit_id` AS `pivot_organisation_unit_id`,
    `organisation_unit_template_categories`.`template_category_id` AS `pivot_template_category_id`
FROM
    `template_categories`
        INNER JOIN
    `organisation_unit_template_categories` ON `template_categories`.`id` = `organisation_unit_template_categories`.`template_category_id`
        INNER JOIN
    `templates` ON `template_categories`.`id` = `templates`.`category_id`
        INNER JOIN
    `template_dimensions` ON `template_dimensions`.`id` = `templates`.`dimensions_id`
WHERE
    `organisation_unit_template_categories`.`organisation_unit_id` = 2
        AND `template_dimensions`.`digital` = 0
        AND `template_categories`.`deleted_at` IS NULL
GROUP BY `template_categories`.`id`

How can I make sure that these two columns:

`organisation_unit_template_categories`.`organisation_unit_id` AS `pivot_organisation_unit_id`,
`organisation_unit_template_categories`.`template_category_id` AS `pivot_template_category_id`

are not included in the query, and bonus point for letting my know why they are implicitly added in the first place.

Many Thanks

Upvotes: 1

Views: 727

Answers (1)

Diogo Sgrillo
Diogo Sgrillo

Reputation: 2701

Our categories also have a scope query, so that we can get all categories which contain at least one template who's dimensions have 'digital = 0'

My suggestion is to rewrite the query to use exists instead of join and group by.

public function scopeIsPrint($query)
{
    return $query
        ->whereExists(function($q) {
            return $q->selectRaw('1')->from('templates')
                     ->join('template_dimensions', 'template_dimensions.id', '=', 'templates.dimensions_id')
                     ->whereRaw('template_categories.id=templates.category_id')
                     ->where('template_dimensions.digital', 0)
        })
}

Upvotes: 1

Related Questions