query on related table instead of pivot

hi guys

so i have many-to-many polymorphic relation between my tables and everything is standard just like the documentation. except i added 'status' on all tables. tables are like this:

posts
    id - integer
    name - string
    status - string
 
videos
    id - integer
    name - string
 	status - string
 
tags
    id - integer
    name - string
	status - string
 
taggables
    tag_id - integer
    taggable_id - integer
    taggable_type - string
    status - string

the problem that i'm facing is when i query the related table like this :

$post->tags()->whereStatus('active')->get()

i get an error cause the 'status' exists both on related and pivot table. error is :

SQLSTATE[HY000]: General error: 1 ambiguous column name: status

i searched a bit and people suggested to include related table name in query like :

$post->tags()->where('tags.status', 'active')->get()

this works ,but is there any way i don't add table name in query ??

i'm searching for a clean method like 'wherePivot()' but on related table instead of pivot . somthing like "whereRelated" !!! any suggestion ??

komeil

2 years ago

Best Answer

Level 1

i ended up adding a macro for it . like this:

 MorphToMany::macro(
'whereRelated', 
function ($column, $operator = null, $value = null) {
     return $this->where(
				$this->getRelated()->getTable() . "." . $column, $operator, $value
	           );
     });

and i use it like this :

$post->tags()->whereRelated('status', 'active')->get()

Snapey

2 years ago

Level 122

$post->tags()->where(tags.status,'active')->get()

komeil

2 years ago

Level 1

@Snapey

thanks, but it was not the answer that i was looking for. btw, you used (tags.status) without quotation. is this a typo or this is the point that you'r making. i'm confused.

Snapey

2 years ago

Level 122

@komeil i'm typing with one finger on an ipad with no syntax highlighting, so yes the field name should be quoted

The point is, in the case of ambiguous column names you can prefix with the table name to get rid of the error

1 like

Please or to participate in this conversation.