Add join to find newest related record and count of same

SQL++
1

Alright, I may be pushing it a little now…

I have this select statement that returns data combined from two documents:

SELECT t2.name, t1.`key`,t1.yearborn,t2.email,
ifmissingornull(t2.cellphone,ifmissingornull(t2.phone,'-')) as phone,
FROM data AS t1
LEFT JOIN data AS t2 ON 'User:Private:' || t1.`key` = META(t2).id AND t2.type='Private' 
WHERE t1.type='User'

Now I want to also find the newest fishing trip and count of all fishing trips by this user using the userkey on the trip. So I have tried something like this (which is not valid syntax):

SELECT t2.name, t1.`key`,t1.yearborn,t2.email,
ifmissingornull(t2.cellphone,ifmissingornull(t2.phone,'-')) as phone,
c
FROM data AS t1
LEFT JOIN data AS t2 ON 'User:Private:' || t1.`key` = META(t2).id AND t2.type='Private' 
let c = select count(*) from data f where f.type='FishingTrip' and f.userkey=t1.`key`
WHERE t1.type='User'

This may be closer but still not valid syntax:

SELECT t2.name, t1.`key`,t1.yearborn,t2.email,
ifmissingornull(t2.cellphone,ifmissingornull(t2.phone,'-')) as phone,
count(t3) count
FROM data AS t1
LEFT JOIN data AS t2 ON 'User:Private:' || t1.`key` = META(t2).id AND t2.type='Private' 
left join data as t3 on t1.`key` = t3.userkey and t3.type='FishingTrip'
WHERE t1.type='User'

The error message is:

image
image1079×112 18.6 KB

There may be users that don’t yet have a fishing trip - so the query must be robust enough to handle that…

Can this be done?

2

I can sort of do them “one-by-one” without the t2 join:

The count grouped by user key:

SELECT t1.`key` userkey,t1.yearborn,count(t2)
FROM data AS t1
LEFT JOIN data AS t2 ON t1.`key` = t2.userkey AND t2.type='FishingTrip' 
WHERE t1.type='User' 
AND (t1.`key` in (select raw p.fieldassistants from data p where p.type='Project' and p.`key` = '12')[0]
OR t1.refishstartdate is valued OR t1.refishstopdate is valued)
group by t1.`key`,t1.yearborn

ahmmm… no, I cannot get the latest fishing trip by user - only for all of the trips with relevant users:

SELECT t1.date
FROM data AS t1
WHERE t1.type='FishingTrip' 
AND t1.userkey in (select raw p.fieldassistants from data p where p.type='Project' and p.`key` = '12')[0]
order by t1.date desc
limit 1

Tried something like this, but it doesn’t find the date…

SELECT t1.userkey,IFMISSINGORNULL(t1.date,[''])[0] date
FROM data AS t1
WHERE t1.type='FishingTrip' 
AND t1.userkey in (select raw p.fieldassistants from data p where p.type='Project' and p.`key` = '12')[0]
group by t1.userkey,date
order by t1.userkey, t1.date desc

I need to be able to limit the result to only the newest date per user…

3 
SELECT t1.`key`
       COUNT(t3) AS ntrips,
       MAX([t1.date, {t1.date, t1.yearborn, udata.name, udata.email, udata.phone}])[1].*
FROM data AS t1
LEFT JOIN data AS t3 ON t1.`key` = t3.userkey AND t3.type = 'FishingTrip'
LET udata = (SELECT up.name, up.email,IFMISSINGORNULL(up.cellphone, IFMISSINGORNULL(up.phone,'-')) AS phone
             FROM data AS up USE KEYS 'User:Private:' || t1.`key`
             WHERE up.type='Private')[0]
WHERE t1.type = 'User'
GROUP BY t1.`key`
ORDER BY t1.`key`, date DESC
4

Very interesting…

I made a small change as it is the date field of FishingTrip that I need to use - so suppose it should be t3.date? But with this code I get an error:

SELECT t1.`key`,
       COUNT(t3) AS ntrips,
       MAX([t3.date, {t3.date, t1.yearborn, udata.name, udata.email, udata.phone}])[1].*
FROM data AS t1
LEFT JOIN data AS t3 ON t1.`key` = t3.userkey AND t3.type = 'FishingTrip'
LET udata = (SELECT up.name, up.email,IFMISSINGORNULL(up.cellphone, IFMISSINGORNULL(up.phone,'-')) AS phone
             FROM data AS up USE KEYS 'User:Private:' || t1.`key`
             WHERE up.type='Private')[0]
WHERE t1.type = 'User'
GROUP BY t1.`key`
ORDER BY t1.`key`, t3.date DESC

The error is:

    "code": 4210,
    "msg": "Expression (`t3`.`date`) must depend only on group keys or aggregates.",

And this is not related to t1 vs. t3 as I tried that :wink:

5

It uses from Projection

ORDER BY t1.key, date DESC

6

Yeah, I started with that - but it gives me this error:

    "code": 3000,
    "msg": "Ambiguous reference to field date.",

And that normally indicates that I’m missing a specific pointer to the data set.

I’m trying this on Community Edition 6.6.0 build 7909 if that matters?

7

hum… MB-46826

Do following.

SELECT t1.`key`,
       COUNT(t3) AS ntrips,
       max.*
FROM default AS t1
LEFT JOIN default AS t3 ON t1.`key` = t3.userkey AND t3.type = 'FishingTrip'
LET udata = (SELECT up.name, up.email,IFMISSINGORNULL(up.cellphone, IFMISSINGORNULL(up.phone,'-')) AS phone
             FROM default AS up USE KEYS 'User:Private:' || t1.`key`
             WHERE up.type='Private')[0]
WHERE t1.type = 'User'
GROUP BY t1.`key`
LETTING max = MAX([t3.date, {t3.date, t1.yearborn, udata.name, udata.email, udata.phone}])[1]
ORDER BY t1.`key`, max.date DESC
1 Like
8

Cool!

Now, more wants more… So I tried to combine another challenge with this where I only get a subset of the users…

WITH fassistants AS (SELECT RAW uf
                     FROM data AS p
                     UNNEST p.fieldassistants AS uf
                     WHERE p.type='Project' AND p.`key` = '17')
SELECT t1.`key`,
       COUNT(t3) AS ntrips,
       max.*
FROM data AS t1
LEFT JOIN data AS t3 ON t1.`key` = t3.userkey AND t3.type = 'FishingTrip'
LET udata = (SELECT up.name, up.email,IFMISSINGORNULL(up.cellphone, IFMISSINGORNULL(up.phone,'-')) AS phone
             FROM data AS up USE KEYS 'User:Private:' || t1.`key`
             WHERE up.type='Private')[0]
WHERE t1.type = 'User' AND t1.`key` IN  fassistants
GROUP BY t1.`key`
LETTING max = MAX([t3.date, {t3.date, t1.yearborn, udata.name, udata.email, udata.phone}])[1]
ORDER BY t1.`key`, max.date DESC

And it WORKS!!! Thanks :+1:

9

…right that wants even more :smiley:

So combining the last challenge (fishery types) and putting it all together:

WITH fassistants AS (SELECT RAW uf
                     FROM data AS p
                     UNNEST p.fieldassistants AS uf
                     WHERE p.type='Project' AND p.`key` = '17')
SELECT t1.`key`,
       COUNT(t3) AS ntrips,
       max.*
FROM data AS t1
LEFT JOIN data AS t3 ON t1.`key` = t3.userkey AND t3.type = 'FishingTrip'
LET udata = (SELECT up.name, up.email,IFMISSINGORNULL(up.cellphone, IFMISSINGORNULL(up.phone,'-')) AS phone
             FROM data AS up USE KEYS 'User:Private:' || t1.`key`
             WHERE up.type='Private')[0],
    fisherytypes = (ARRAY v.fisherytype
       FOR v IN t1.fisheryinterests 
       WHEN v.fisherytype IS NOT NULL END)
WHERE t1.type = 'User' AND t1.`key` IN  fassistants
GROUP BY t1.`key`
LETTING max = MAX([t3.date, {t3.date, t1.yearborn, udata.name, udata.email, udata.phone, fisherytypes}])[1]
ORDER BY t1.`key`, max.date DESC

In human words: “Find all users in project ‘17’ and show their private info (name, email, etc.) together with the fishery types and the number of fishing trips as well as the date of the latest trip”

Amazing! Thanks a bunch :+1:

Just putting the final result here for someone else who may need to do similar queries :wink: