SELECT b.locationName FROM transport
b USE KEYS (SELECT RAW META().id FROM transport
WHERE type=’_transport_location’ AND ifmissing(deleted, false) = false)
JOIN transport
k ON KEYS (
select Raw MAX([lastUpdatedAt, id])[1] as id from (select MAX([updatedAt, updatedAt])[1] as lastUpdatedAt,MAX([updatedAt, locationName])[1] as locationName,
MAX([updatedAt, meta().id])[1] as _id from transport
where type = ‘_transport_task’ and locationName is not null and locationName NOT IN [‘OOTY’]
and status in [‘START’, ‘END’] and ifmissing(deleted, false) = false group by locationName
union
select MAX([updatedAt, updatedAt])[1] as lastUpdatedAt, MAX([updatedAt, destinationlocationName])[1] as locationName, MAX([updatedAt, meta().id])[1] as _id from transport
where type = ‘_transport_task’ and destinationlocationName is not null and destinationlocationName NOT IN [‘OOTY’]
and status in [‘START’, ‘END’] and ifmissing(deleted, false) = false group by destinationlocationName) as i group by locationName
)
Please provide relation with sample documents.
Use the following query and JOIN with _transport_location document based on your releation
SELECT MAX([d.UpdatedAt, d])[1].*
FROM (SELECT updatedAt, locationName, META().id
FROM transport
WHERE type = "_transport_task"
AND locationName NOT IN ["OOTY"]
AND status IN ["START", "END"] AND IFMISSING(deleted, false) = false
UNION
SELECT updatedAt, destinationlocationName AS locationName, META().id
FROM transport
WHERE type = "_transport_task"
AND destinationlocationName NOT IN ["OOTY"]
AND status IN ["START", "END"] AND IFMISSING(deleted, false) = false
) AS d
GROUP BY d.locationName
)
Sorry, For not being clear.
I have a transport database. Where it tracks the journeys of the users.
I have list of locations for this transport database.
For admin we need to show that which user is visited the last location.
Here
_transport_location → Locations document type we support in transport database
_transport_task → Journeys document type that user creates a journey
Requirement :-
I need to get all locations include the last known Journey.
I know it can be achieved with Ansi right hand in 6.6 but how can we achieve the same using CB 6.0
SELECT b.locationName, b.user.userName, b.status, b.updatedAt FROM transport
b USE KEYS (SELECT RAW META().id FROM transport
WHERE type=’_transport_location’ AND ifmissing(deleted, false) = false)
JOIN transport
k ON KEYS (
select Raw MAX([lastUpdatedAt, id])[1] as id from (select MAX([updatedAt, updatedAt])[1] as lastUpdatedAt,MAX([updatedAt, locationName])[1] as locationName,
MAX([updatedAt, meta().id])[1] as _id from transport
where type = ‘_transport_task’ and locationName is not null and locationName NOT IN [‘OOTY’]
and status in [‘START’, ‘END’] and ifmissing(deleted, false) = false group by locationName
union
select MAX([updatedAt, updatedAt])[1] as lastUpdatedAt, MAX([updatedAt, destinationlocationName])[1] as locationName, MAX([updatedAt, meta().id])[1] as _id from transport
where type = ‘_transport_task’ and destinationlocationName is not null and destinationlocationName NOT IN [‘OOTY’]
and status in [‘START’, ‘END’] and ifmissing(deleted, false) = false group by destinationlocationName) as i group by locationName
)
Location Document
{
“coordinates”: ,
“createdAt”: “2021-02-19T08:24:34.222Z”,
“locationName”: “OOTY”,
“priorityForLocation”: “High”,
“status”: “ACTIVE”,
“type”: “_transport_location”,
“updatedAt”: “2021-02-25T12:03:48.134Z”
}
Journey Document
{
“createdAt”: “2021-03-15T06:14:11.307Z”,
“destinationLocation”: {
“_id”: “location:OOTY”,
“locationName”: “OOTY”,
“status”: “ACTIVE”
},
“location”: {
“_id”: “location:DELHI”,
“locationName”: “DELHI”,
“status”: “ACTIVE”
},
“operator”: {
“_id”: “user:OGRICK”,
“firstName”: “Rick”,
“lastName”: “Sanchez”,
“username”: “ogrick”
},
“plannedEndDate”: “2021-03-15T08:13:53.896Z”,
“plannedStartDate”: “2021-03-15T06:13:53.896Z”,
“actualStartDate”: “2021-03-15T06:13:53.896Z”,
“status”: “START”,
“type”: “_transport_task”,
“updatedAt”: “2021-03-15T06:14:11.307Z”,
}
Output Document
{
“locationName”: “OOTY”,
“userName”: “ogrick”
“status”: “END”,
“updatedAt”: “2021-03-10T05:16:47.732Z”
}
CB 6.0 right side of JOIN requires the bucket.
You can use UNNEST and do join.
OR
SELECT tl.*, ARRAY v FOR v IN tltasks WHEN v.locationName = tl.locationName END AS users
FROM transport AS tl
LET tltasks = SELECT MAX([d.UpdatedAt, d])[1].*
FROM (SELECT updatedAt, locationName, META().id
FROM transport
WHERE type = "_transport_task"
AND locationName NOT IN ["OOTY"]
AND status IN ["START", "END"] AND IFMISSING(deleted, false) = false
UNION
SELECT updatedAt, destinationlocationName AS locationName, META().id
FROM transport
WHERE type = "_transport_task"
AND destinationlocationName NOT IN ["OOTY"]
AND status IN ["START", "END"] AND IFMISSING(deleted, false) = false
) AS d
GROUP BY d.locationName
)
WHERE tl.type = "_transport_location" AND ifmissing(tl.deleted, false) = false