Hi everyone, does anyone knows about raw query fea...
# helpo
owyah
06/12/2022, 1:57 AMHi everyone, does anyone knows about raw query feature in javascript version?
n
Needle
06/12/2022, 1:57 AMHello @owyah!
This thread has been automatically created from your message in #843999948717555735 a few seconds ago.
We have already mentioned the @User so that they can see your message and help you as soon as possible!
Want to unsubscribe from this thread?
Right-click the thread in Discord (or use the ``...`` menu) and select "Leave Thread" to unsubscribe from future updates.
Want to change the title?
Use the ``/title`` command!
We have solved your problem?
Click the button below to archive it.
g
garyaustin
06/12/2022, 2:16 AMDo you have more information on what you are referring to? The closest thing is using rpc calls to postgres functions.
o
owyah
06/12/2022, 2:37 AMI read the problem in Github, so its not available now, right?
owyah
06/12/2022, 2:37 AMYes I have been trying using RPC, but it doesn't work
owyah
06/12/2022, 2:38 AMWhen I use this query, its working:
``select * from user_transactions(1,1);``
owyah
06/12/2022, 2:38 AMbut if I use RPC, its not working:
``const {data, error} = await db.rpc("user_transactions", {p_group_id: 1, p_payment_id: 1});``
owyah
06/12/2022, 2:39 AMthe parameter, doesn't work
owyah
06/12/2022, 2:43 AM``
CREATE OR REPLACE FUNCTION user_transactions (p_group_id bigint, p_payment_id bigint)
RETURNS TABLE (
id int8,
first_name VARCHAR,
mid_name VARCHAR,
last_name varchar,
pp varchar,
total int8
)
AS $$
BEGIN
RETURN QUERY select p.id, p.first_name, p.mid_name, p.last_name, p.pp, sum(t.price)::int8 as total
from profiles p
left join transactions t on t.user_id = p.id
and t.payment_id = p_payment_id
where p.group = p_group_id
group by p.id, p.first_name, p.mid_name, p.last_name, p.pp
order by p.first_name;
END; $$
LANGUAGE 'plpgsql';
``
owyah
06/12/2022, 2:44 AMthat is my query to create the function
owyah
06/12/2022, 2:46 AMI must create a function because of this:
``left join transactions t on t.user_id = p.id
and t.payment_id = p_payment_id``
s
silentworks
06/12/2022, 3:13 PMWhat error are you getting? have you tried running the query on its own inside of the SQL Editor?
o
owyah
06/13/2022, 12:05 AMYes it is, there is no error, but we got different records, when I use SQL Editor it is right, but if I use ``db.rpc`` it is not working
owyah
06/13/2022, 1:34 AMOr maybe @silentworks and @garyaustin have any idea how to use this query using supabase javascript
``
select p.id, p.first_name, p.mid_name, p.last_name, p.pp, sum(t.price)::int8 as total
from profiles p
left join transactions t on t.user_id = p.id
and t.payment_id = p_payment_id
where p.group = p_group_id
group by p.id, p.first_name, p.mid_name, p.last_name, p.pp
order by p.first_name;
``
g
garyaustin
06/13/2022, 1:41 AMYou will not be able to run anything close to that in JS client code. You need to use a rpc call and a postgres function. If it worked in SQL editor then you likely have a RLS permission issue. Do you have RLS on any tables? Or something in your rpc call format is wrong, but I don't see anything obvious in your code above.
Can you clarify what "it is not working" means? No data, some data in each record, wrong records?
o
owyah
06/13/2022, 2:08 PMFor example, when I pass p_group_id = 2, when I use SQL editor, its working (return p_group_id 2). But if I use RPC, the result is not p_group_id 2 but p_group_id 1
owyah
06/13/2022, 2:09 PManother example, ``sum(t.price)::int8 as total`` not working using RPC, but it's working using SQL Editor
s
silentworks
06/13/2022, 3:34 PMWhat happens when you call the function you created inside of the SQL Editor?
o
owyah
06/16/2022, 1:22 AMthe result is ok: get p_group_id 2
owyah
06/16/2022, 1:22 AMthe result is as I expected
owyah
06/16/2022, 1:30 AMbut not while using rpc call