Joining multiple arrays about yalinqo HOT 5 CLOSED

If amount of data you're working with isn't small, I'd advise to use RDBMS like MySQL. YaLinqo is supposed to be used when a database isn't avalaible or adequate, not to replace databases. You'll just lose performance, write more code and gain nothing in return.

The problem with joining is that functional syntax of LINQ supports joining only two arrays. In order to join more arrays, you'll need to create temporary arrays or objects between joins which is quite verbose and, well, annoying. In .NET, query syntax can be used, which performs all these transformations for you, but it isn't available in PHP.

You also don't need to wrap arrays in "from" if you don't perform any operations on them, YaLinqo accepts arrays as arguments in all methods.

If you don't understand how some method works, you can just look at the code, in most cases it's pretty simple. Let's look at join (it's actually one of the most complex methods). Without boilerplate, its code looks like this:

$lookup = $inner->toLookup($innerKeySelector);
foreach ($this as $ok => $ov) {
    $key = $outerKeySelector($ov, $ok);
    if (isset($lookup[$key]))
        foreach ($lookup[$key] as $iv)
            yield $resultSelectorKey($ov, $iv, $key) => $resultSelectorValue($ov, $iv, $key);
}

So, it creates a lookup (array matching keys to many values), then iterates over outer sequence matching keys.

One bonus of PHP is that merging arrays is simple. So, if you have two arrays with indexes which are either unique or equal, you can use array_merge to join the results. If some keys are common, one more array can be added as an argument to "override" collapsed keys. So the general pattern is this:

$join = from($as)
    ->join($bs,
        '$v["keya"]', '$v["keya"]',
        function ($a, $b) { return array_merge($a, $b); })
    ->join($cs,
        '$v["keyb"]', '$v["keyb"]',
        function ($ab, $c) { return array_merge($ab, $c); })
    ->join($ds,
        '$v["keyc"]', '$v["keyc"]',
        function ($abc, $d) { return array_merge($abc, $d, [
            'v1' => $abc['v1'],
            'v2' => $d['v2'],
        ]); });

One more reason of losing values is calling toArray instead of toList, as multiple join method calls can result in equal keys.

P. S. Not sure whether this is what you're doing — there's too much code. I hope my advice can simplify your code considerably.

from yalinqo.

Thank you @Athari . The response was quite fast!
I have a loop that cycles about 1000 times. And each time it makes request to database. I was trying to optimze it. I think I went by wrong way :-(

from yalinqo.

Thank you @Athari . The response was quite fast!
I separated joines, and changed toArray to toList, now everything is ok!
Thank you!

from yalinqo.

@anvarulugov Getting rid of RDBMS may result in performance improvements if you're working with a high-performance language like C++ and you know how to perform your queries optimally on a low level. Reimplementing RDBMS in PHP will never make your program faster, and even if it does, there's likely something which can be done on RDBMS level which can do much better, like adding indexes and giving hints to optimizer.

In your case, the obvious solution is to not perform the same query 1000 times. If all you're chaning is two arguments in WHERE statement, just query all data you need with one query, then perform filtering in your program. This is where YaLinqo can actually be useful.

Unless you have enormous volume of data, one query with filtering on app level will be faster than thousand queries — you'll lose more time on inter-process communication than on actual queries.

from yalinqo.

Yes, you are right, that's why I got all the data in to arrays, from 4 tables into 4 arrays, and used YaLinqo. @Athari, you are genius. I replaced my code with yours now the code looks like this:

$rs = from( $mlp_cropplan )
        ->where(function( $mlp_cropplan ) { 
            global $TillageID, $ZoneID;
            return ( ( $mlp_cropplan['TillageID'] == 0 || $mlp_cropplan['TillageID'] == $TillageID ) && ( $mlp_cropplan['ZoneID'] == 0 || $mlp_cropplan['ZoneID'] == $ZoneID ) ); 
        } )
        ->orderBy( function( $mlp_cropplan ) { return $mlp_cropplan['CropID']; } )
        ->thenBy( function( $mlp_cropplan ) { return $mlp_cropplan['CropID']; } )
        ->join( $mlp_actions, 
            '$v["ActionID"]', '$v["ActionID"]',
            function ($a, $b) { return array_merge($a, $b); })
        ->join( $mlp_actiontypes, 
            '$v["ActionTypeID"]', '$v["ActionTypeID"]',
            function ($a, $b) { return array(
                'CropID' => $a['CropID'],
                'OperID' => $a['OperID'],
                'ParentOperID' => $a['ParentOperID'],
                'ActionID' => $a['ActionID'],
                'WorkloadUnit' => $a['WorkloadUnit'],
                'WorkpayUnit' => $a['WorkpayUnit'],
                'ActionTypeID' => $b['ActionTypeID'],
                'LaborProd' => $a['Labor_prod'],
                'LaborRate' => $a['Labor_rate'],
                'ServiceRate' => $a['Mech_rate'],
                'WorkCategory' => $b['Category'],
                'Feature' => $a['Feature'],
                'StartYear' => $a['StartYear'],
                'StartMonth' => $a['StartMonth'],
                'StartDay' => $a['StartDay'],
                'EndYear' => $a['EndYear'],
                'EndMonth' => $a['EndMonth'],
                'EndDay' => $a['EndDay'],
            ); });

$mlp_cropplan = $rs2->toList();
print_r($mlp_cropplan);

from yalinqo.