sql problem @ mylist select with different title types
Posted: Wed Mar 24, 2004 7:59 pm
After the different title types have been implemented there is quite some demand for a profile option to select the prefered title language used @ mylist.
The problem is that only the main jap romaji title (type=1) is always present. The other titles are not added for some animes.
This means that the selects which generate the mylist overview need to be updated in order to select the desired title (i.e. type=10) and fallback to the main title (type=1) if no title of the desired type exists.
The main concern here is 1) acceptable speed, 2) correct sorting.
The database is PostgreSQL 7.4.1.
listcachetb holds one row per anime a user has in mylist.
animetb holds all anime data except the title.
animetitletb holds all anime titles (the int2 type field is of interest here, for each anime there is exactly one type=1 title, and at most one type=10,20 or 30 title, there are also type=2 and type=3 titles but they are not of interest here).
These are the current selects used to generate the mylist:
there has been some discussion on irc about this already and one of the possible solutions was:
That works somewhat, however postgres is not capable of using the aname value for sorting via lower() or the regexp operator.
And it might also be slow, haven't checked that yet.
So feel free to post suggestions how to solve this, preferably including SQL statements![Razz :P](./images/smilies/icon_razz.gif)
BYe!
EXP
The problem is that only the main jap romaji title (type=1) is always present. The other titles are not added for some animes.
This means that the selects which generate the mylist overview need to be updated in order to select the desired title (i.e. type=10) and fallback to the main title (type=1) if no title of the desired type exists.
The main concern here is 1) acceptable speed, 2) correct sorting.
The database is PostgreSQL 7.4.1.
listcachetb holds one row per anime a user has in mylist.
animetb holds all anime data except the title.
animetitletb holds all anime titles (the int2 type field is of interest here, for each anime there is exactly one type=1 title, and at most one type=10,20 or 30 title, there are also type=2 and type=3 titles but they are not of interest here).
These are the current selects used to generate the mylist:
Code: Select all
SELECT c.aid,c.epcnt,c.seen,c.spepcnt,c.spseen,c.state,at.name AS aname,a.eps AS aeps,a.rating AS arating,a.votes AS avotes,a.atts,a.attavg FROM listcachetb AS c,animetitletb AS at,animetb AS a WHERE c.uid=1 AND c.aid=a.id AND c.aid=at.aid AND at.type=1 ORDER BY lower(at.name) ASC
SELECT c.aid,c.epcnt,c.seen,c.spepcnt,c.spseen,c.state,at.name AS aname,a.eps AS aeps,a.rating AS arating,a.votes AS avotes,a.atts,a.attavg FROM listcachetb AS c,animetitletb AS at,animetb AS a WHERE c.uid=1 AND c.aid=a.id AND c.aid=at.aid AND at.type=1 AND at.name ~* '^e' ORDER BY lower(at.name) ASC
Code: Select all
SELECT
c.aid,c.epcnt,c.seen,c.spepcnt,c.spseen,c.state,COALESCE(at2.name, at1.name) AS aname,
a.eps AS aeps,a.rating AS arating,a.votes AS avotes,a.atts,a.attavg
FROM
animetitletb AS at1 LEFT JOIN animetitletb AS at2
ON (at1.aid=at2.aid AND at1.type=1 AND at2.type=11)
INNER JOIN listcachetb AS c ON (c.aid=at1.aid)
INNER JOIN animetb AS a ON (a.id=at1.aid)
WHERE
at1.type=1 AND c.uid=1 AND a.id=357
And it might also be slow, haven't checked that yet.
So feel free to post suggestions how to solve this, preferably including SQL statements
![Razz :P](./images/smilies/icon_razz.gif)
BYe!
EXP