How Do I Use .where() in a Server Side Action to Return a Single Value?

Antony · May 21, 2020, 12:44pm

I’m trying to obtain a single id value from a larger database query on the server side.

I have a database query lookups, and I want the id value when the lookup_function field has a specific value… so since the where() still returns an array then I want to first element of what it returns.

I have tried using the user interface (with experimental on) and am managing to get two different results:

lookup

Neither of them look quite correct to me…

@Teodor, can you tell me which one is correct, or if I need some other syntax and the user interface has created something wrong in 2.9.1?

Teodor · May 21, 2020, 1:08pm

Not sure how you end up with such code, but in the collections formatters there is a Top formatter. Use it to get the top 1 record returned.

code generated:

JonL · May 21, 2020, 1:14pm

Did you add [0] somewhere in the expression builder @Antony?
If so you will end with those expressions. You can only use that directly in the code. In the expression builder you have to go with top and last which give you more flexibility as they will take into account the length of the array when using last for instance.

Remember the formatters are server model agnostic. So if you decide to change it you don’t have to figure out where you hardcoded things. I tend to forget that too

Antony · May 21, 2020, 2:44pm

@JonL, I didn’t change anything… those two piece of code came straight from Wappler 2.9.1!

@Teodor, am I right in saying the code I showed is not legal? If so, then I can change this to be a bug report…

Antony · May 21, 2020, 2:46pm

Teodor...

.top(1) return a one element array.

I need the id of the item returned by .top(1) so what would the code look like for that?

JonL · May 21, 2020, 2:46pm

Oh. Then probably a video on how you got there would be useful

Indeed that expression is nothing but broken unless you have a variable named id0 which we know you don’t

Antony · May 21, 2020, 2:47pm

Yes, Wappler totally created that...

JonL · May 21, 2020, 2:53pm

The problem with your request is that you specifically asked for :

So Teo's answer is quite right

But it seems what you actually need is the value of the parent.

Antony · May 21, 2020, 2:56pm

Seems pretty clear to me. Single integer value required!

bpj · May 21, 2020, 3:08pm

So I think (and I may well be wrong) that if you use the set value (e.g. with name test) using the Top as @Teodor suggested and need the id, you could use the valueName.id (e.g. test.id). If you’re setting an array in a value, it won’t currently parse the array in the data picker, you will have to specify the key yourself.

JonL · May 21, 2020, 3:10pm

Not clear at all. Believe me. At least not for me.
Where is that single integer value stored?

Antony · May 21, 2020, 5:10pm

It is an assignment to a "Set Session" as shown in my original post!

JonL · May 21, 2020, 5:13pm

Antony, it’s getting more complicated by the post.

I can only see two set values in your OP and you are stating that they are incorrectly formed, ergo I do not know what to make about them.

Stored as in Database if not clear.

Antony · May 21, 2020, 5:16pm

Well I just want to say this:

Set Session test1 = the id value from the first row found in the table `lookups` where column `lookup_function` == 'banana'

(I know there is only one value of banana available to find)

Does that make more sense?

JonL · May 21, 2020, 5:18pm

lookups.where_shit.top(1).id

Antony · May 21, 2020, 5:19pm

But surely .top(1) returns an array of 1 element so I need a [0] somewhere?

(and remember this is server side. I can do it client side with my eyes closed!)

Teodor · May 21, 2020, 5:20pm

top(1) means something[0] in the code … it’s the same.

Antony · May 21, 2020, 5:21pm

Okay… I’ll try it!

set session test1 = lookups.where(...).top(1).id

Antony · May 21, 2020, 5:50pm

Not working!

Assigning a session variable:

{{lookups.where(lookups.lookup_function, "==", "stripe_fee_category").top(1).id}}

Gives me the error:

{code: 0, file: "C:\Users\anton\Dropbox\workshop-angel\dmxConnectLib\lib\formatters\collections.php",…}
code: 0
file: "C:\Users\anton\Dropbox\workshop-angel\dmxConnectLib\lib\formatters\collections.php"
line: 35
message: "Undefined index: "
trace: "#0 C:\Users\anton\Dropbox\workshop-angel\dmxConnectLib\lib\formatters\collections.php(35): exception_error_handler(8, 'Undefined index...', 'C:\\Users\\anton\\...', 35, Array)↵#1 [internal function]: lib\core\formatter_where(Array, NULL, '==', 'stripe_fee_cate...')↵#2 C:\Users\anton\Dropbox\workshop-angel\dmxConnectLib\lib\core\Parser.php(396): call_user_func_array('\\lib\\core\\forma...', Array)↵#3 C:\Users\anton\Dropbox\workshop-angel\dmxConnectLib\lib\core\Parser.php(354): lib\core\Parser->objectMember(Object(Closure))↵#4 C:\Users\anton\Dropbox\workshop-angel\dmxConnectLib\lib\core\Parser.php(296): lib\core\Parser->primary()↵#5 C:\Users\anton\Dropbox\workshop-angel\dmxConnectLib\lib\core\Parser.php(273): lib\core\Parser->group()↵#6 C:\Users\anton\Dropbox\workshop-angel\dmxConnectLib\lib\core\Parser.php(253): lib\core\Parser->unary()↵#7 C:\Users\anton\Dropbox\workshop-angel\dmxConnectLib\lib\core\Parser.php(243): lib\core\Parser->multiplicative()↵#8 C:\Users\anton\Dropbox\workshop-angel\dmxConnectLib\lib\core\Parser.php(233): lib\core\Parser->addictive()↵#9 C:\Users\anton\Dropbox\workshop-angel\dmxConnectLib\lib\core\Parser.php(223): lib\core\Parser->bitwiseShift()↵#10 C:\Users\anton\Dropbox\workshop-angel\dmxConnectLib\lib\core\Parser.php(213): lib\core\Parser->relational()↵#11 C:\Users\anton\Dropbox\workshop-angel\dmxConnectLib\lib\core\Parser.php(203): lib\core\Parser->equality()↵#12 C:\Users\anton\Dropbox\workshop-angel\dmxConnectLib\lib\core\Parser.php(193): lib\core\Parser->bitwiseAnd()↵#13 C:\Users\anton\Dropbox\workshop-angel\dmxConnectLib\lib\core\Parser.php(183): lib\core\Parser->bitwiseXor()↵#14 C:\Users\anton\Dropbox\workshop-angel\dmxConnectLib\lib\core\Parser.php(173): lib\core\Parser->bitwiseOr()↵#15 C:\Users\anton\Dropbox\workshop-angel\dmxConnectLib\lib\core\Parser.php(161): lib\core\Parser->logicalAnd()↵#16 C:\Users\anton\Dropbox\workshop-angel\dmxConnectLib\lib\core\Parser.php(145): lib\core\Parser->logicalOr()↵#17 C:\Users\anton\Dropbox\workshop-angel\dmxConnectLib\lib\core\Parser.php(141): lib\core\Parser->conditional()↵#18 C:\Users\anton\Dropbox\workshop-angel\dmxConnectLib\lib\core\Parser.php(86): lib\core\Parser->expression()↵#19 C:\Users\anton\Dropbox\workshop-angel\dmxConnectLib\lib\App.php(221): lib\core\Parser->parse('lookups.where(l...', NULL)↵#20 C:\Users\anton\Dropbox\workshop-angel\dmxConnectLib\modules\core.php(109): lib\App->parseObject('{{lookups.where...')↵#21 C:\Users\anton\Dropbox\workshop-angel\dmxConnectLib\lib\App.php(173): modules\core->setsession(Object(stdClass), 's_expense_strip...')↵#22 C:\Users\anton\Dropbox\workshop-angel\dmxConnectLib\lib\App.php(137): lib\App->execSteps(Object(stdClass))↵#23 C:\Users\anton\Dropbox\workshop-angel\dmxConnectLib\lib\App.php(107): lib\App->execSteps(Array)↵#24 C:\Users\anton\Dropbox\workshop-angel\dmxConnectLib\lib\App.php(72): lib\App->exec(Object(stdClass))↵#25 C:\Users\anton\Dropbox\workshop-angel\dmxConnect\api\subscriber_user_settings\set_session_variables.php(8): lib\App->define(Object(stdClass))↵#26 {main}"

JonL · May 21, 2020, 5:57pm

What is the output of that?