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

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… :thinking:

@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?

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:

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 :slight_smile:

@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…

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?

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

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

Yes, Wappler totally created that…

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

So Teo’s answer is quite right :slight_smile:

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

Seems pretty clear to me. Single integer value required!

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.

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

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

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.

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?

lookups.where_shit.top(1).id

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

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

Okay… I’ll try it! :slight_smile:

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

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

What is the output of that?