what is the sqlite_orm equivalent to ifnull(X,Y) ?

[juandent]

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

[juandent]

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>;

[juandent]

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") 

??

[fnc12]

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.

[fnc12]

@juandent ifnull is on its way #721. Next I'll add explicit from

[fnc12]

@juandent ifnull is merged. Now you can replace coalesce with ifnull to make your query more consistent. I'll add explicit from next

[juandent]

Thanks!!! In what branch? master?

[fnc12]

dev

[juandent]

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"

[fnc12]

yes it is. I am thinking about proper API

[juandent]

good!! 👍