I am wondering if there is a way to get not only the newest record but also the recordcount without an additional query. Using the sample
WITH data AS ( [{"_type":"farm","Record":{"apn":"65338104", "PropertyAddress": "1 Infinite Loop; Cupertino, CA 95014"}},
{"_type":"farm","Record":{"apn":"65338108", "PropertyAddress": "2 Infinite Loop; Cupertino, CA 95014"}},
{"_type":"listing", "ParcelNumber":"653-38-104", "Record":{"Dates":{"ModificationTimestamp":"2022-03-28T21:26:45.594-07:00"}}},
{"_type":"listing", "ParcelNumber":"653-38-104", "Record":{"Dates":{"ModificationTimestamp":"2022-02-28T21:26:45.594-07:00"}}}
])
i can use the below query to get this info but I am not sure thats the most effective way as it seems to be an extra Querry.
SELECT f.Record.PropertyAddress AS farm,
(
SELECT p1.ParcelNumber, p1.Record
FROM p AS p1
ORDER BY p1.Record.Dates.ModificationTimestamp DESC
LIMIT 1)[0] AS lisiting,
(
SELECT RAW count(*)
FROM p AS p1)[0] AS lisitingCount
FROM data AS f
LEFT NEST data AS p ON f.Record.apn = REPLACE(p.ParcelNumber, "-", "")
AND p._type = "listing"
WHERE f._type = "farm"
LIMIT 2;
Also, how could i change the where clause to only return Docs that lets say have more then 1 listingCount
SELECT d.farm,
(
SELECT p1.ParcelNumber, p1.Record
FROM d.p AS p1
ORDER BY p1.Record.Dates.ModificationTimestamp DESC
LIMIT 1
)[0] AS lisiting,
ARRAY_LENGTH(d.p) AS lisitingCount
FROM (SELECT f.Record.PropertyAddress AS farm, p
FROM data AS f
LEFT NEST data AS p ON f.Record.apn = REPLACE(p.ParcelNumber, "-", "") AND p._type = "listing"
WHERE f._type = "farm") AS d
WHERE ARRAY_LENGTH(d.p) > 1
LIMIT 2;
since this is another select query for the from clause do i actually save time using this vs running it like my query ? Both have 3 Query’s ?
But i guess i need it if i want to select only records that have more then ) or so listings
N1QL always streams results when the row(document ) ready. When you do results.getrow() (not sure exact api, i am not expert on client) Those gives when available.
Very high level
Example:
SELECT d.*
FROM default d
WHERE d.x = 10;
If you do EXPLAIN, you see various operators. Each operator works on different thread in parallel
In above case IndexScan, Fetch, Filter, InitialProjection, FinalProjection,Stream
each one works different document. Once operator done with document it sends to next operator.
As document reach Stream it sends to client.
For example Order, GROUP operators are blocking because those has to holds results to compute (i.e. all input must exast before it releases first document)
Ok i gave the sample you provided a try but didn’t get any data back. I then removed the where filter for Array > 0 which got me records galore. The interesting part is that i got records which had listingCount : 0
but no other data. In my case the rets bucket / listing has 5 million records and the Contacts / farm has 5k
Here is the query i used
SELECT d.farm,
(
SELECT p1.ParcelNumber,
p1.Record
FROM d.p AS p1
ORDER BY p1.Record.Dates.ModificationTimestamp DESC
LIMIT 1 )[0] AS lisiting,
ARRAY_LENGTH(d.p) AS lisitingCount
FROM (
SELECT f.Record.PropertyAddress AS farm,
p
FROM Contacts AS f LEFT NEST rets AS p ON f.Record.apn = REPLACE(p.ParcelNumber, "-", "")
AND p._type = "listing"
WHERE f._type = "farm") AS d
WHERE ARRAY_LENGTH(d.p) > 1
LIMIT 2;
You are doing LEFT NEST and doing in parent ARRAY_LENGTH(d.p) > 1 (which eliminates null projected)
Just do NEST and see what you get. Without data not able to tell why no results. Query seems right.