Fetch object by plain SQL query with SORM

Question

Is it possible to fetch items by plain SQL query instead of building query by DSL using SORM?

For example is there an API for making something like

val metallica = Db.query[Artist].fromString("SELECT * FROM artist WHERE name = ?", "Metallica").fetchOne() // Option[Artist]

instead of

val metallica = Db.query[Artist].whereEqual("name", "Metallica").fetchOne() // Option[Artist]

Jolly

I dug around his source code, and couldn't find anything. There is a JDBC class that contains an executeQuery method, but I couldn't figure out how one might use it on an existing instance. – Ahders

Thanks for the first question! – Fini

@DominicBou-Samra Next time just ask a question here - it is a strongly encouraged approach. SORM was intentionally designed not to be used with its guts pulled out, so in most cases digging in internal APIs won't help you. – Fini

Answer 1

Since populating an entity with collections and other structured values involves fetching data from multiple tables in an unjoinable way, the API for fetching it directly will most probably never get exposed. However another approach to this problem is currently being considered.

Here's how it could be implemented:

val artists : Seq[Artist] 
  = Db.fetchWithSql[Artist]("SELECT id FROM artist WHERE name = ?", "Metallica")

If this issue gets a notable support either here or, even better, here, it will probably get implemented in the next minor release.

Update

implemented in 0.3.1

Fini

May be select should query all rows (I mean *)? – Jolly

@AndreyKouznetsov I think you meant columns. No. The thing is SORM performs queries in two phases: first it applies all your filters, orders and other clauses to select the matching ids of the entity you're fetching, then it queries for all the contents of this entity. With the "SELECT id" approach we could replace the first phase. Anyway, you've created an issue - let's discuss all details there. I'm actually writing a reply there now. – Fini

Answer 2

If you want to fetch only one object (by 2 and more arguments) you can also do the following:

by using Sorm Querier

Db.query[Artist].where(Querier.And(Querier.Equal("name", "Name"), Querier.Equal("surname", "surname"))).fetchOne()

or just

Db.query[Artist].whereEqual("name", "Name").whereEqual( "surname","surname").fetchOne()

Crinoline