Hi,
My self_hosted baserow is running with an external sql server.
Now I’d like to read baserow data with an SQL select query.
The table I’m using contains a link-to-table field and I couldn’t figure out how to retrieve the value of that field. I don’t know what to join the main table (database_table_xxx) with.
Perhaps you can provide me with a example ?
Hey 
So there is a database_field table which has all the fields of your Baserow instance.
When you look at your database_table_xxx table you will see that it has columns called field_<some_id> where <some_id> is the id of the field in the database_field table.
Now once you found your field in the database_field table you can then find the linked row field in the database_linkrowfield table where field_ptr_id is the id of the field.
So given a field of ID 3585 if you can get the id of the field from your database table you can simply do something like:
SELECT * FROM database_linkrowfield where field_ptr_id = 3585
Or if you also want the field data:
SELECT * FROM database_linkrowfield LEFT JOIN database_field on database_linkrowfield.field_ptr_id = database_field.id WHERE database_linkrowfield.field_ptr_id = 3585
Ok thanks for the example.
My understanding is that to get the value from the link-to-table field you have to join the database_linkrowfield table with the database_relation_xxx table that contains the value selected by the user.
Does that mean that you have to use dynamic table name to replace xxx with the number that match the link_row_relation_id ?
Example:
In my database_linkrowfield table, field n.2652 points to link_row_relation_id n.317
The only way to get the value for field 2652 on a specific row is to read table database_relation_317 ?
Does that mean that you have to use dynamic table name to replace xxx with the number that match the link_row_relation_id ?
Yes I think that is the case, I am not the most knowledgable person on the team in regards to SQL, so maybe there is a way to dynamically generate that table name in your query.
But yes the observation you made is correct. The link_row_relation_id is an id that points to another table, in that table you will find 3 columns id (just an id), tableXXXmodel_id (which is the row id of the table with the link row field) and tableXXXmodel_id (which is the row id of the row that it is linking to).
So if you want to for example get all the values of a related row, you would need to go through the relation table to get the row id of the row it is linking to and then you would need to go to the database_table_xxx of the table that it is linking to where you will find the row using it’s ID.
Does that all make sense? Please ask more questions if anything is unclear, I understand that us generating database tables dynamically makes it sometimes hard to reason about these problems, I am still getting to grips with it as well 
It starts to get a bit clearer
My previous post was misleading, I don’t think I need to use dynamic table name to achieve my goal.
Here’s a complete example.
I have a Owner grid and a Car grid
The car grid contains a link-to-table field that indicates the owner of the car.
I want my SQL select to display the same view as my car grid where each rows has the car and its owner.
Inside my baserow database, :
-
database_table_318 contains the owner grid rows
-
database_table_319 contains the car grid rows
-
From the database_field table I know that field 2652 is linked to table_id 319
-
From the database_linkrowfield table I know that field 2652 is a link-to-table type and that database_relation_317 contains its “value”
-
database_relation_317 can be use to match the car grid row id with the value registered for the link-to-table-field
Knowing this, I set up the following query :
select dt319.id, dt319.field_2649, dr.table318model_id as OwnerID, dt318.field_2646 as OwnerName
from database_table_319 dt319
left join database_relation_317 dr
on dt319.id = dr.table319model_id
left join database_table_318 dt318
on dt318.id = dr.table318model_id
where dt319.trashed is false
order by dt319.id
And the result looks ok to me.
At first, what is really confusing is that inside database_table_319 there’s absolutely no reference to the link-to-table field (field_2652) at all !
Just so you know, I’m need to plug a Metabase interface on Baserow.
Yes I ran this on my end as well to make sure that we are on the same page,
(for reference)
select * from database_table_181 as table_1
LEFT JOIN database_relation_745 on table_1.id = database_relation_745.table181model_id
LEFT JOIN database_table_748 as table_2 on database_relation_745.table748model_id = table_2.id
WHERE table_1.trashed = false
So I think it does what you wanted it to do right?
Ok, thanks for the confirmation 