list: group on foreign key value

usr338

Hi, is there any way to group data displayed in list by a foreign key ?( foreign key does not display as available columns for list)
for example i have list of people that settle in multiple building in different times. i want to group people by name and building id (foreign key here) and then get last date of residency in each building.
and (other question is )how can group by multiple column in sub query and then use one column value for filtering ?

gabriel

We do not offer foreign keys for many data query options as they are often very numerous and are almost never useful to be interacted with directly. In such a scenario, as you are describing, using foreign keys to group by might be useful - though you could also group by labels such as the building name; or if building name is not unqiue, by building + some other criteria (like location etc.).

As far as I know, you cannot group by multiple attributes in sub queries. Only one attribute can be chosen for a sub query to return and only this attribute can currently be used for aggregation/grouping. This is a technical limitation and I am not aware of a workaround.

usr338

gabriel Thanks for response. As you said ,in that example it is possible to use other column to reach the goal but in some cases it is not possible(or may be some extra join to other tables needed)

usr338

hi again, in above example how can get the record of last residency date and place of each person in list field? I joined tables something like this:
select p.anme , r.date, b.name from person p left join residency r on p.id = r.personId left join building b on r.buildingId = b.id
then i tried to group by person name and use Max aggregate on residency date and use sub query to fetch building name related to last date of residency inside list field. but every time i get error for generated query.
can you please explain me how can i reach the goal in builder form designer? thanks in advance.

gabriel

The only thing I can say from your explanation, is that you cannot use grouping on the parent query, and then connect a sub query to the result, probably using a filter for a relationship attribute. I would suggest that you do not mix groupings and sub queries in this case.

I cannot provide any further assistence as I can barely parse your request. If you need further assistance, please take some time and clearly write out what you are trying to achieve. A list of the relevant entities (relations, attributes) and relationships would be helpful as well.

usr338

gabriel Ok,i'll try to make it clear, so let me describe the real senario: I have relation called people that has some contact information such as forename, surname, mobile number, email and ...
also there is another relation that called building and has some information about building such as name, unit count and ... but for now only name matter.
also there is another relation called building_people_relation that hold how people and building related to each other in different times. it include from_date , people (n:1 relation), building (n:1 relation) and relation_type(n:1 relation).
relation_type is another relation holding predefined value like: guest, owner, resident and ...
it means that many people can be related to many building during time.
what is important to me is the last relation of each person to buildings. how can i get those records to show inside a list?
for example if we have following data in building_people_relation :
joe 2024/01/01 building_no_1 resident
joe 2024/05/10 building _no_1 owner
tom 2024/03/03 building _no_1 guest
tom 2024/03/03 building_no_2 resident
tom 2024/09/08 building_no_2 owner

i want to show following data in list :

joe 2024/05/10 building _no_1 owner
tom 2024/03/03 building _no_1 guest
tom 2024/09/08 building_no_2 owner
i tried to group list columns on people and building and use max aggregate function on from_date .. but i dont know how to get the correct relation type! (how ever it is easy to reach the goal using sql )

gabriel

One way to get your desired result:

You start with people as base relation (0).
You join relation building_people as (1) via 1) building_people.people.
You filter relation building_people: 1) building_people.id = sub query, which is:
- Base relation (0): building_people
- Returned attribute: 0) building_people.id
- Filter: sub query 0) building_people.people = main query 1) building_people.people
- Order by: 0) building_people.from_date descending
- Fixed result limit: 1
You join building & relation_type to the filtered relation 1) building_people.
You then select your desired attributes.

The most important part is the sub query, looking up only the latest record from building_people for the relevant person.

usr338

gabriel Thank you very much for your time and attention. it is exactly what i wanted but couldn't get it using subqueries.