Possibility of making joins in "Data explorer"

[jeromeperez]

It might be interesting to add the possibility of making joins in "Data explorer".
table activity (type_activity, domain_activity)
examples:
1 - activity_type is not null or domain_activity is not null
2 - activity_type equals activity1 or activity_type equals activity2

[seancolsen]

@jeromeperez thanks for the idea! I think my reply in #3533 should cover this too. Joining arbitrary tables together (without foreign key constraints) is a feature request we've gotten multiple times. It's another thing we're considering as we rethink the Data Explorer.

[jeromeperez]

It would be interesting to be able to do different joins with SQL, but it would be even better if users who don't know SQL could create explorations simply by using or.
For example :
Display the names of Roman archaeological sites in France or in Italy

[seancolsen]

Display the names of Roman archaeological sites in France or in Italy

Thanks for giving an example. This is helpful. Help me understand more though... What does the schema look like in this example? Here are some structures I can imagine:

• (A) Maybe the data is in one table like this:

  • sites

    id	name	country
    1	Colosseum	Italy
    2	Pompeii	Italy
    3	Ephesus	Turkey
    4	Pont du Gard	France

  Then, to satisfy your example we'd want the Data Explorer to generate a query like this:

  SELECT *
  FROM sites
  WHERE country = 'Italy' OR country = 'France';

  As far as I know the Data Explorer is not currently able to handle OR conditions like this. Is this the limitation you're encountering? If so, I absolutely agree that this is an important case for the Data Explorer to handle!

  But I wouldn't call this "joining" — only "filtering". Just filtering using different boolean operators, AND vs OR.

  If you really are talking about "joining" in the SQL sense, then I'd want to consider an example like (B)...

• (B) Maybe the data is in multiple tables like this:

  • sites

    id	name	country
    1	Colosseum	97
    2	Pompeii	97
    3	Ephesus	81
    4	Pont du Gard	42

  • countries

    id	name
    97	Italy
    81	Turkey
    42	France

  Assuming that sites has a foreign key constraint referencing countries, the Data Explorer already allows you to reference data from both tables in the same query. This is what I would call "joining".

  Are you running into a limitation with this? If so, I'd like to better understand it.

From your other comment, it sounds like you might be comfortable with SQL yourself. If that's the case, it might be helpful for us to speak in terms of SQL queries, for this discussion and other potential discussions in the future. For example, you could say something like "here's an SQL query that I'd like the Data Explorer to be capable of producing". Even if you'd like to arrive at that SQL via the form-based UI, I think it would still help to see the precise SQL you're talking about, if possible. This is just a small suggestion though, in order to help reduce the risk of things getting lost in translation.

[jeromeperez]

• (A) it should be interesting for my searchers to be able to filtering using different boolean operators, AND vs OR vs XOR
• (B)
  individuals (individual_id,name,sex)
  sightings(individual_id,date,present,troop_id,is_dead)

1. Bit more complicated:

SELECT S1.troop_ID as "Troop FirstObs"
	, ind.name as "Name"
	 , ind.sex as "Sex"
     , S1.date as "First observation" 
	 , S1.present as "Present FirstObs"
	 , S2.troop_ID as "Troop LastObs"
     , S2.date as "Last observation"	 
	 , S2.present as "Present LastObs"
FROM individuals as ind, sightings  S1, sightings  S2
WHERE 
(
S1.individual_ID = ind.individual_ID
AND S2.individual_ID = ind.individual_ID
AND S2.individual_ID = S1.individual_ID -- ajout de la transitivité au cas où
)
AND NOT EXISTS 
(SELECT 1 FROM sightings as S1b WHERE S1.date > S1b.date and S1.individual_ID = S1b.individual_ID )
AND NOT EXISTS 
(SELECT 1 FROM sightings as S2b WHERE S2.date < S2b.date and S1.individual_ID = S2b.individual_ID )

order by ind.name
       , S1.date
       , S2.date
;

2.Very complicated:

-- Utilisation du fenêtrage Window
SELECT distinct
first_value(troop_ID) OVER w as "Troop FirstObs",
first_value(name) OVER w as "Name FirstObs", 
first_value(sex) OVER w as "Sex FirstObs",
first_value(date) OVER w as "First observation",
first_value(present) OVER w as "Present FirstObs", 
last_value(troop_ID) OVER w as "Troop LastObs",
last_value(present) OVER w as "Present LastObs",
last_value(is_dead) OVER w as "Dead LastObs",
last_value(date) OVER w as "Last observation"
FROM individuals, sightings
where sightings.individual_ID = individuals.individual_ID
WINDOW w AS (PARTITION BY sightings.individual_ID, sightings.troop_ID ORDER BY troop_ID,name,date ROWS between unbounded preceding and unbounded following)
;

[seancolsen]

Thanks @jeromeperez. These are great examples! To me, this reinforces the need for us to support SQL in the Data Explorer. I have a really had time imagining how we'd ever create an easy-to-use visual query builder to construct queries like this. But supporting SQL should be feasible. My hope is that we'll be able to work on that feature after Mathesar's beta release later this year, but I can't promise.