Dumas: Count of Monte Cristo

[LenaHenningsen]

I just had a look on the database results on the homepage: what happened to the Count of Monte Cristo? I can't find him there, but didn't he have 10 hits or so?? Thanks much!

[duncdrum]

@whalekeykeeper can you follow up on this W0084 is the work in question

[whalekeykeeper]

Sure. Will have a look today.

[duncdrum]

@whalekeykeeper did you follow up on this?

[duncdrum]

ok to get to the bottom of the case of the missing count;

the sql query responsible for constructing the view data is here.

select distinct a.act_id, a.act_object, ps.title from Act a, PrimarySource ps inner join PrimarySource on a.act_object = ps.prim_source_id  where ps.title_lang = a.id_lang and a.act_object!='W0414'

while two lines below the view in the screenshot above contains the correct data

select distinct a.act_id, a.act_object, w.creator, (select distinct ps.title from PrimarySource ps where a.act_object = ps.prim_source_id) as title, (select distinct coalesce(p.family_name, '') || ' ' || coalesce(p.first_name, '') from Person p where w.creator = p.person_id) as name from Act a, Work w inner join Work on a.act_object = w.work_id where a.act_object!='W0414'

we need to adjust the first query to return the correct results. Alternatively replace the sql query with an xquery run via basex (that outputs csv) and queries the xml files instead of the csv.

[whalekeykeeper]

checked the returned value for both SQL queries.
Returned by the first query:

Returned by the second query:

[whalekeykeeper]

I think the problem is:
title_lang for Le Comte de Monte-Cristo is fr, while the SQL query has where clause where ps.title_lang = a.id_lang.
All the values in the id_lang field in Act.csv are en.
Therefore, the SQL query will not return a book name in French.

[duncdrum]

well done, thank you