Is there a convenient way to construct a query in raw SQL?

[nerdrew]

Example query I'd like to run:

select hosts.id, hosts.hostname, hosts.created_at, hosts.updated_at
from hosts
left join logs on hosts.id = logs.host_id and logs.app_id = ?
group by hosts.hostname, hosts.id
order by count(logs.app_id) asc
limit 1

I'd like to be able to select the query above into a Host model.

Follow up question: is there a way to represent the above query in diesel? I couldn't figure it out.

The subject stands though, even if/when there is a way to represent the query in diesel's DSL. There are times when it is clearer to write sql and it would be nice if diesel had a convenient story for supporting that use case.

[sgrif]

Yes, there's a function for writing raw SQL (though its use is discouraged as you lose the vast majority of the safety benefits the query builder provides). http://sgrif.github.io/diesel/diesel/expression/dsl/fn.sql.html

This query written using the query builder is basically one-to-one with the equivalent SQL.

use diesel::prelude::*;
use diesel::expression::{sql, count};
use hosts::dsl::*;

hosts.select(id, hostname, created_at, updated_at)
    .left_outer_join(logs::table)
    .filter(logs::app_id.eq(whatever))
    .group_by((hostname, id))
    .order(count(logs::app_id).asc())
    .limit(1)

[nerdrew]

Follow up question: is it possible to have multiple joins? I see a test for a join through, but what about cases where there isn't a through?

What I want:

logs::table.select((logs::id, users::id, hosts::id))
    .inner_join(hosts::table)
    .inner_join(users::table)
    .limit(1)

[sgrif]

No, this is a major limitation at the moment. We need specialization to do
this safely. There's an issue open for this but I'm on my phone and can't
find a link at the moment

On Fri, Mar 18, 2016, 11:22 AM Andrew Ryan Lazarus notifications@github.com
wrote:

Follow up question: is it possible to have multiple joins? I see a test
for a join through, but what about cases where there isn't a through?

What I want:

logs::table.select((logs::id, users::id, hosts::id))
.inner_join(hosts::table)
.inner_join(users::table)
.limit(1)

—
You are receiving this because you modified the open/close state.
Reply to this email directly or view it on GitHub
#231 (comment)