How to add JSON_TABLE to the end of the FROM section in AR in a query?

Question

Yii

Hello!
There is an artist table in the database with a data_json field that stores json, I want to use AR to add the following code to the end of the FROM section:

JSON_TABLE(data_json, '$.songs[*]' COLUMNS (
  name VARCHAR(128) PATH '$.name',
  duration INT PATH '$.duration'
 )
) songs

I write like this:

$query->from([
'artist',
'JSON_TABLE(data_json, '$.songs[*]' COLUMNS (
  name VARCHAR(128) PATH '$.name',
  duration INT PATH '$.duration'
 )
) songs'
])

And everything works, but if you add a left join to the request, then the request breaks, because left join is added to the end of the FROM section and applied to the JSON_TABLE.
Question: how to tell AR to add JSON_TABLE to the end of FROM?

Answer 1

I don't know how your case works, but try this query to make subqueries.

$subQuery = Model::find()->from(...); // JSON_TABLE

$query = Model::find()->from($subQuery)->join(...);