JOINing with another Aura.SqlQuery?

[adrianmiu]

Does this library allow for such compositions? If it does, I wasn't able to find it in the documentation.

[harikt]

I don't know your exact requirement or this works for you.

The one you produced query can be converted to string and if possible attached to the query ? I also wonder how it will work passing the params. Have not tried this before.

[adrianmiu]

@harikt I want to be able to execute something like this

select 
*
from users
join (
   select
   author_id,
   count(books) as books_count
   from books
   group by author_id
   having books_count>10
) as prolific_authors on users.id=prolific_authors.author_id

or

select 
*
from users
where user_id in (
   select
   author_id
   from books
   where year>2017
)

I know the second example could be refactored to not require a subselect, I'm just providing it as an example

[pmjones]

@adrianmiu IIRC you should be able to:

/** @var SelectInterface $joinSelect */
$select->joinSubSelect("LEFT", $joinSelect, 'as_alias', 'ON foo = bar')

Let me know if that helps!

[adrianmiu]

@pmjones That's what I want. Any reason why the API is not simplified it does not require using a different method? Also, is there something like this?

$select->whereIn('column', $subselect)

[pmjones]

Any reason why the API is not simplified it does not require using a different method?

The fromSubSelect and joinSubSelect need that extra subselect argument, which changes the signature from the non-subselect methods enough to require a new method. (IIRC.)

is there something like this?

Wow, I think there actually is:

$select->where("foo IN(?)", $subSelectObject);

Cf. https://github.com/auraphp/Aura.SqlQuery/blob/2.x/src/AbstractQuery.php#L400-L408

(Funny how much of your own code you can forget.)

[adrianmiu]

nice. thank you!

I gather Atlas ORM doesn't use the subselects to optimize loading of recordsets. For example if you where to select the users that have at least 5 comments, one could use the subselect to count and retrieve the matching users sparing a trip to the database. Or does this count as "premature optimization"? :)

[pmjones]

Believe it or not, I think you can actually do that too, though I have not tried it:

$atlas->select(UsersMapper::CLASS)
    ->joinSubSelect('INNER', 'SELECT COUNT(*) AS number FROM comments GROUP BY user_id', 'comment_count', 'comments.user_id = users.user_id')
    ->where('comment_count.number > 5')
    ->fetchRecordSet();

(Edited.)

[pmjones]

@adrianmiu If that answers all your questions, please close the issue at your convenience. If not, let me know!