Hi all,
Ive been going round on circles on this for 2 days but im sure its pretty easy so appreciate your expertise. The concept is simple and is effectively a list of URL’s where you can choose your favourites.
-
Any user can add a new URL
-
Any user can favourite their own URL or anyone elses
-
in url_favourite table user_id and url_id combined are defined as unique
i have 3 tables
User(id, username)
Url(id, url, user_id)
Url_Favourite(id, url_id, user_id, favourited)
Relations
User->hasMany->Url(s)
Url->hasMany->Url_Favourite(s)
Url_Favourite->hasOne->User
Url_Favourite->hasOne->Url
I want to display a DataProvider where all the URLs are shown but any that have been favourited appear at the top
e.g.
|URL|Favourited|
|...| Y|
|...| N|
|...| N|
I know the query would be pretty much:
Select url.url, url_favourite.favourited from url LEFT JOIN url_favourite uf ON uf.url_id=url.id AND uf.user_id=<LoggedinUserID>
When trying to do this via Active Record im hitting all sorts of issues
Ive manually added a relation trying to pass in the dynamic logged in user id but it never seems to get set, in GridView calling urlUserFavourite.favourited returns UnknownPropertyException
/**
* GII created
*/
public function getUrlFavourites()
{
return $this->hasMany(UrlFavourite::className(), ['url_id' => 'id']);
}
/**
* I Added
*/
public function getUrlUserFavourite()
{
return $this->hasOne(UrlFavourite::className(), ['url_id' => 'id', 'user_id'=>\Yii::$app->user->id]);
}
i have added to my DataProvider a leftJoin where i can set the user id but i cant get it to map to the url.id as it treats it as a string i.e. SQL generated is … ON urlFav.user_id = 1 AND urlFav.url_id = ‘id’… id should be the current URL model id. Ive also tried here going through my created relation or the GII created one
$query = Url::find();
$query->with('urlFavourites')
->leftJoin(UrlFavourite::tableName().' urlFav', ['urlFav.user_id'=>\Yii::$app->user->id, 'urlFav.url_id' => 'id']);
Im pretty sure im just missing something obvious so any help is greatly appreciated!
Many thanks