Where in the MySQL database schema is the URL alias for a group relation?

Question

I am building a report outside of Drupal that pulls data from a Drupal 8 database. The report shows titles of different nodes and I want to link those titles to the web page for that content. This is working fine if I use the standard /node/X path, but I would like to use the URL alias. I don't want the url alias of the node itself (which I did find thanks to this Which mysql tables contain the URL Alias path data? post). Our content is related to groups, so I want to use the URL alias that is created as part of the group entity relation.

At first I checked the url_alias table, using this query to look for the node id in the source field, but I didn't find the alias set up for that node :

select u.alias 
from url_alias u
where u.source LIKE '%4806%'

Then I tried to check the group tables. I only find 5 tables starting with group in the MySQL schema, and I didn't find the URL alias for the entity reference in any of them:

• group_content
• group_content__group_roles
• group_content_field_data
• groups
• groups_field_data

Does anyone know where that URL alias from the group relation is stored in the MySQL schema?

Answer 1

As Clive stated in the comment, all aliases are in the url_alias table.

You should join it with the og_membership table, to take the url alias of the node's group.

So, for a node with id 100, the script should look like:

select u.alias 
from og_membership og, url_alias u
where concat('node/', og.gid) = u.source
and og.etid = 100 and og.group_type like 'node'

Answer 2

url_alias is path_alias in drupal 8

select n.nid, n.vid, n.type, pa.path, pa.alias, pa.status
from node n 
inner join path_alias pa on pa.path = concat('/node/', n.nid);