How to do subquery within SELECT list without joining table?

[jamesleeht]

I've looked into the subquery part of the wiki and tried a bunch of things but unable to achieve something similar to this:

SELECT car.name,
(SELECT count(*) FROM car_like WHERE car_like.car_id == car.id) AS like_count,
FROM car

Is there any way to achieve something like this? (Select within the select, rather than joining the table before selecting)

I tried this:

likeCountTbl := SELECT(COUNT(STAR).AS("like_count")).FROM(CarLike).WHERE(CarLike.CarID.EQ(Car.ID)).AsTable("lct")
likeCount := IntegerColumn("like_count").From(likeCountTbl)

However, placing this likeCount variable in the projection list just causes an error to be thrown (presumably because there is no table to reference). What is another way to achieve this?

Any help would be appreciated!

[houten11]

AsTable is used only for the sub-queries in the FROM clause.

stmt := SELECT(
	Car.Name,
	SELECT(
		COUNT(STAR),
	).FROM(
		CarLike,
	).WHERE(
		CarLike.CarID = Car.ID,
	).AS("like_count")
).FROM(
	Car,
)

[jamesleeht]

Thanks! this worked - not sure why i didn't consider this option as I initially thought AS was intended for aliases