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
BYe!
EXP