XF 2.1 Good and better way for an SELECT, UPDATE, INSERT

[Robert9]

I have read more than one time in the last days that someone has updated his add-on with the better/faster version of getting data.
While i need an easy select now, i can do that, but i dont know on which way is should do that.

Is the way with finder the good one? Do i need to set up something before i use it?
I have seen some definitions in different scripts.

 

[TickTackk]

While i need an easy select now, i can do that, but i dont know on which way is should do that.

Depends on the table.

Is the way with finder the good one?

It depends on what you're trying to achieve.

Do i need to set up something before i use it?

Finder needs entity to work with.

 

[Robert9]

$db = \XF::db();
$var1= $db->fetchOne('SELECT * FROM xf_purchase_request
WHERE
provider_metadataIndex = '$state->subscriberId' AND
cost_amount = '$state->cost');

OR

WHERE
provider_metadataIndex = ?, $state->subscriberId AND
cost_amount = ?, $state->cost');

OR

$var1 = \XF::finder('XF:xf_purchase_request')
                                ->where('provider_metadataIndex', $state->subscriberId)
                                ->where('cost_amount',$state->cost)
                                ->fetchOne();


OR

$var1 = \XF::em()->findOne('XF:purchaseRequest', ['provider_metadataIndex' => $state->subscriberId, 'cost_amount',$state->cost ]);

Normally i would use this one, but should i use it?

$db = \XF::db();
$var1= $db->fetchOne('
SELECT request_key
FROM xf_purchase_request
WHERE provider_metadataIndex = $state->subscriberId
AND cost_amount =$state->cost');

$value = $var1['request_key'];

 

[Xon]

Inline query building like the first example is horribly bad. Please do not do that.

Use the XF2 finder as it will construct the query for you, and return XF entities which are the recommended way of doing any updates

 

[Robert9]

Thank you, that is what i want to know.

$var1 = \XF::finder('XF:xf_purchase_request')
                                ->where('provider_metadataIndex', $state->subscriberId)
                                ->where('cost_amount',$state->cost)
                                ->fetchOne();

Very often or maybe always(?) i see something like this, but without selecting a field.



It doesnt matter if i selecting millions of times in a day

SELECT * instead of SELECT field1, field2 ?



What is the syntax to fetch only fields, please?

$var1 = \XF::finder('XF:xf_purchase_request')->request_key->where ...

OR

$var1 = \XF::finder('XF:xf_purchase_request')->where ...
->fetchOne()->request_key;




And this one:
fetch()->toArray();

i can use?

One of the main differences between the direct query approach and using the Finder is that the base unit of data returned by the Finder is not an array. In the case of a Finder object which calls the fetchOne method (which only returns a single row from the database), a single Entity object will be returned.

I still dont see the need for an object instead of an array for my small needs (maybe because i still dont understand the difference) and maybe because i dont need protection and things like that.)

I have only small problems to solve like

if node.depth == 2, $x=parent_node_id, else $x=node_id
$y=siblings($x); $z=children($x);
and many times i just just no idea what is happen and not happen here with these objects, while an array is something nice and handy for me.

 

[TickTackk]

The point of using finder is so you get entity (fetchOne()) or collection of entity (fetch()). When you get a collection of entity, you loop through each one of them and do what you would do if you got a single entity.

fetch()->toArray();

There is no need for that unless you want to store the data of each of the entities you receive in a cache or something.

I highly recommend you get a real IDE like PHPStorm and use it to play around XF source code in a local environment.

 

[Robert9]

I use Komodo (windows) with XENDEBUG at a old Lenovo Notebook (ubuntu);
but this here i have to test in real life tomorrow, i hope it is correct now:

        // if status== 9, VAR1 is needed!
        if($state->eventType == '9') {
            // fetch key, where provider_metadata and cost
                $var1 = \XF::finder('XF:xf_purchase_request')
                                ->where('provider_metadataIndex', $state->subscriberId)
                                ->where('cost_amount',$state->cost)
                                ->fetchOne();

                if($var1) {
                                $state->requestKey = $var1->request_key;
                }
                else
                {
                                $state->logType = 'error';
                                $state->logMessage = 'OK=30 Status=9, cant find key';
                                return false;
                }
        }


        if($state->eventType == '1' || $state->eventType == '7'  || $state->eventType == '9' )
        {
                // $safe = $state->transactionId;
                // unique! should not be found in log
                $state->transactionId .= time();
        }

I would like to reduce the query to requestKey, but i still havent found an example.