sqlite_orm
sqlite_orm copied to clipboard
fnc12
what is the sqlite_orm equivalent to ifnull(X,Y) ?
I have this select: Can it be written in sqlite_orm? In particular how can we deal with ifnull function?
SELECT c.*, IFNULL(i.response_1_count, 0) AS response_1_count
FROM Claims c
LEFT JOIN
(
SELECT fkey_claim, COUNT(*) AS response_1_count
FROM Invoices
WHERE fkey_INSResponse = 1
GROUP BY fkey_claim
) AS i
ON i.fkey_claim = c.id_claim
I found coalesce is the substitution for ifnull. But I am at a loss as to how to write this SQL in sqlite_orm... could you give me a hand here?
I have the following code:
struct response_1_count : alias_tag {
static const std::string& get() {
static const std::string res = "response_1_count";
return res;
}
};
struct i : alias_tag {
static const std::string& get() {
static const std::string res = "i";
return res;
}
};
and this:
auto lines = storage.select(columns(alias_column<als_c>(&Claim::id), as<response_1_count>(coalesce<int>(alias_column<als_c>(&Claim::other_system_id),0))),
as<i>(left_join<als_i>(on(c(alias_column<als_i>(&Invoice::)))
); ///??????
where:
using als_c = alias_c<Claim>;
using als_p = alias_p<Patient>;
using als_d = alias_d<Doctor>;
using als_s = alias_s<Specialty>;
using als_m = alias_m<Medication>;
using als_i = alias_i<Invoice>;
using als_j = alias_j<INSResponse>;
using als_k = alias_k<INSResponseLine>;
using als_l = alias_l<Claim>;
using als_q = alias_q<Invoice>;
I have this going in the right direction but still need to insert the nested select:
auto lines = storage.select(columns(alias_column<als_c>(&Claim::id), as<response_1_count>(coalesce<int>(alias_column<als_c>(&Claim::other_system_id), 0))),
left_join<als_i>(on(c(alias_column<als_i>(&Invoice::fkey_claim)) == alias_column<als_c>(&Claim::id))));
This produces the following SQL:
SELECT 'c'."id_claim", COALESCE('c'."other_system_id", ?) AS response_1_count
FROM 'Claims' c
LEFT JOIN 'Invoices' 'i' ON ('i'."fkey_claim" = 'c'."id_claim")
??
Oh man
LEFT JOIN
(
SELECT fkey_claim, COUNT(*) AS response_1_count
FROM Invoices
WHERE fkey_INSResponse = 1
GROUP BY fkey_claim
)
is not supported right now. left_join<T> can accept only a template argument which must mean a type mapped to a storage.
Actually I even did not know about this feature in SQLite. I can add it soon. Let's leave this issue opened until it is implemented. It may look like: left_join(select(...)).
IFNULL also is not implemented but it can be replaced using COALESCE as you noticed before. You can inspect functions implementation status here.
@juandent ifnull is on its way https://github.com/fnc12/sqlite_orm/pull/721. Next I'll add explicit from
@juandent ifnull is merged. Now you can replace coalesce with ifnull to make your query more consistent. I'll add explicit from next
is this still current:
"LEFT JOIN ( SELECT fkey_claim, COUNT(*) AS response_1_count FROM Invoices WHERE fkey_INSResponse = 1 GROUP BY fkey_claim ) is not supported right now"