[Question] How one could implement JOIN with a subquery?

[iVovolk]

First of all, thank you for your hard work. The question is, how to write a query like below using Exposed

SELECT *
FROM categories
      LEFT JOIN products ON products.id = (
             SELECT products.id 
             FROM products 
             WHERE products.category_id = categories.id 
             LIMIT 1
     )
LIMIT 10

I'd found related issue but it didn't help

[TheReprator]

@iVovolk did you got the solution, if yes please share

[Tapac]

There is eqSubQuery operator in Exposed, you can use it like:

Categories.join(Products, JoinType.LEFT) {
   Products.id eqSubQuery Products.slice(Products.id).select { Products.id eq Categories.id).limit(1)
}

But I think in that case it's more efficient to not use subquery. afaiu the code below will give same result.

SELECT * 
FROM categories 
   LEFT JOIN products on categories.id = products.category_id
LIMIT 10

[TheReprator]

@Tapac , thanks for the quick response, then, how can i achieve it with alias?

[iVovolk]

@Tapac thahks for the response. Eventually i came up with the similar solution by rewriting my query. I was asking because in my case the query with a subquery wokrked a bit faster than without. Anyway, the proplem is solved! Thanks.

[Tapac]

@TheReprator, can you clarify what kind of alias do you mean?

[TheReprator]

means, i am talking about nested queries https://github.com/JetBrains/Exposed/wiki/DSL#where-expression @Tapac

[Tapac]

Please provide raw SQL and I'll try to show how to convert it into Exposed DSL