Porting Z4 app to Z5

chriseana

Hi Folks,
I'm building a qualitative analysis software based on Zotero 4. It's working perfectly, now I need to port it to Z5. The last thing I need to do it's to make this query work as before. I tried every way. Please, can someone help on that?

That's the query:

SELECT
i.itemID AS itemID
, SUBSTR( d.value,1,4 ) AS itemdate
,
key as zoteroKey_sufix
,
title.value As title
,
t.typeName As typeName
,
url.value As url
,
c1.firstName As author_1_first
,
c1.lastName As author_1_last
,
c2.firstName As author_2_first
,
c2.lastName As author_2_last
,
c3.firstName As author_3_first
,
c3.lastName As author_3_last
,
extra.value As extra
FROM items i
LEFT JOIN itemDataValues title
ON
title.valueID =
(
SELECT
itemData.valueID
FROM itemData
WHERE itemData.fieldID =
(
SELECT
fieldID
FROM fields
WHERE fields.fieldName = "title" LIMIT 1
)
AND itemData.itemID = i.itemID LIMIT 1
)
LEFT JOIN itemTypes t
ON t.itemTypeID = i.itemTypeID
LEFT JOIN itemDataValues url
ON
url.valueID =
(
SELECT
itemData.valueID
FROM itemData
WHERE itemData.fieldID =
(
SELECT
fieldID
FROM fields
WHERE fields.fieldName = "url" LIMIT 1
)
AND itemData.itemID = i.itemID LIMIT 1
)
LEFT JOIN itemDataValues d
ON
d.valueID =
(
SELECT
itemData.valueID
FROM itemData
WHERE itemData.fieldID =
(
SELECT
fieldID
FROM fields
WHERE fields.fieldName = "date" LIMIT 1
)
AND itemData.itemID = i.itemID LIMIT 1
)
LEFT JOIN creators c1
ON c1.creatorID =
(
SELECT
creatorID
FROM itemCreators
WHERE itemID = i.itemID
ORDER BY orderIndex LIMIT 0 , 1
)
LEFT JOIN creators c2
ON c2.creatorID =
(
SELECT
creatorID
FROM itemCreators
WHERE itemID = i.itemID
ORDER BY orderIndex LIMIT 1 , 1
)
LEFT JOIN creators c3
ON c3.creatorID =
(
SELECT
creatorID
FROM itemCreators
WHERE itemID = i.itemID
ORDER BY orderIndex LIMIT 2 , 1
)
LEFT JOIN deletedItems
ON i.itemID = deletedItems.itemID
LEFT JOIN itemDataValues extra
ON
extra.valueID =
(
SELECT
itemData.valueID
FROM itemData
WHERE itemData.fieldID =
(
SELECT
fieldID
FROM fields
WHERE fields.fieldName = "extra" LIMIT 1
)
AND itemData.itemID = i.itemID LIMIT 1
)
WHERE deletedItems.itemID Is NULL
AND t.typeName <> "attachment"

bwiernik

You might get faster responses by posting development questions like these to: https://groups.google.com/forum/#!forum/zotero-dev

chriseana

Thank you @bwiernik