sql problem @ mylist select with different title types

Want to help out? Need help accessing the AniDB API? This is the place to ask questions.

Moderator: AniDB

Locked
User avatar
exp
Site Admin
Posts: 2438
Joined: Tue Oct 01, 2002 9:42 pm
Location: Nowhere

sql problem @ mylist select with different title types

Post by exp » 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:

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
there has been some discussion on irc about this already and one of the possible solutions was:

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
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 :P

BYe!
EXP

Elberet
Posts: 778
Joined: Sat Jul 19, 2003 8:14 pm

Post by Elberet » Wed Mar 24, 2004 10:39 pm

Can I see an example of the query with the ORDER BY clause? The mention of "regexp" is kinda confusing here, since REGEXP is a comparision operator iirc. Anyway, ordering should work by putting the function call as an alias into the SELECT block and using the alias in the ORDER BY clause. However, the code will have to be able to do with the additional column and should discard it.

I've also rewritten the joins. I believe that it's a wiser choice to begin with the table with the least rows and left-join all other required data in from there. By left-joining at1 at the end, I avoid the additional "at1.type=1" in the WHERE block. Stuff that is for testing only is /* commented */ out.

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,
    LOWER(aname) as sortname
FROM
    listcachetb AS c
    LEFT JOIN animetb AS a ON (a.id=c.aid)
    LEFT JOIN animetitletb AS at1 ON (at1.aid=a.id AND at1.type=1)
    LEFT JOIN animetitletb AS at2 ON (at2.aid=a.id AND at2.type=11)
WHERE
    c.uid=1 /*AND a.id=357*/
ORDER BY sortname ASC

User avatar
exp
Site Admin
Posts: 2438
Joined: Tue Oct 01, 2002 9:42 pm
Location: Nowhere

Post by exp » Wed Mar 24, 2004 10:47 pm

with regexp i mean the where part which limits the results to a given subset.
i.e.

Code: Select all

at.name ~* '^e'
as it seems postgresql does NOT allow the use of aliases there.
i.e. replacing at.name with aname would give you and SQL exception.
so as i see it atm one would probably have to include that part twice once for at1 and once for at2 which would be kinda ugly.

BYe!
EXP

Elberet
Posts: 778
Joined: Sat Jul 19, 2003 8:14 pm

Post by Elberet » Wed Mar 24, 2004 11:16 pm

Updated:

I removed the regexp in favor of yet another additional column that is built by taking the leftmost character of the result of the LOWER function. Depending on how much overhead this removes (regexp parsers are huge monsters...), it might even be an advantage - but I'm not sure.

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,
    LOWER(aname) as q_sortname,
    LEFT(aname, 1) as q_firstchar
FROM
    listcachetb AS c
    LEFT JOIN animetb AS a ON (a.id=c.aid)
    LEFT JOIN animetitletb AS at1 ON (at1.aid=a.id AND at1.type=1)
    LEFT JOIN animetitletb AS at2 ON (at2.aid=a.id AND at2.type=11)
WHERE
    c.uid=1 AND q_firstchar='x' /*AND a.id=357*/
ORDER BY q_sortname ASC

User avatar
exp
Site Admin
Posts: 2438
Joined: Tue Oct 01, 2002 9:42 pm
Location: Nowhere

Post by exp » Thu Mar 25, 2004 3:10 pm

thx to you all.

it works now.

current version:

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,
    LOWER(COALESCE(at2.name, at1.name)) as q_sortname
FROM
    listcachetb AS c
    LEFT JOIN animetb AS a ON (a.id=c.aid)
    LEFT JOIN animetitletb AS at1 ON (at1.aid=a.id AND at1.type=1)
    LEFT JOIN animetitletb AS at2 ON (at2.aid=a.id AND at2.type=20)
WHERE
    c.uid=1 AND LOWER(SUBSTR(COALESCE(at2.name, at1.name),1,1))='x'
ORDER BY
    q_sortname ASC
BYe!
EXP

Elberet
Posts: 778
Joined: Sat Jul 19, 2003 8:14 pm

Post by Elberet » Thu Mar 25, 2004 5:32 pm

Wow, pgsql really doesn't let you re-use a select expression's alias in where or order-by clauses? One more reason to stick to mysql... ^_^

Locked