XF 2.1 Finder and Query only pulling 10 results

[Cupara]

I have no use of limit in my results so I don't understand why it is only grabbing 10 results when there is 24 results total for the table. It should be running through them to update them based on data I received from a JSON request to Blizzard's WoW API.

Here is my finder:

$guildRoster = $guildFinder->where('char_level', '>', '10')->fetch();

Here is my query I tried with after the finder only did 10 results:

public function getRoster()
    {
        $db = \XF::db();

        return $db->fetchAll('
            SELECT * FROM xwow_roster
            WHERE char_level > 10');
    }

Thanks in advance for the help and taking a look for me.

 

[nocte]

You can also do this in order to debug:

$guildRoster = $guildFinder->where('char_level', '>', '10');

$sql = $guildRoster->getQuery();

\XF::dump($sql);

 

[Cupara]

Thanks but I have debugged, that is how I knew it was only grabbing 10, that is all it lists is 10 results instead of 24.

EDIT: I did try something since you suggested using getQuery() but now I get invalid argument supplied foreach().

 

[Lukas W.]

EDIT: I did try something since you suggested using getQuery() but now I get invalid argument supplied foreach().

getQuery literally gets the query, as a string, for debugging. Passing that into a foreach will have it fail.

 

[Cupara]

I figured that would be the case. Still, have the issue of the finder only grabbing 10 results instead of the full amount.

 

[Chris D]

Still, have the issue of the finder only grabbing 10 results instead of the full amount.

Obviously you still have the issue. The steps posted above are to try and help you troubleshoot the issue.

$guildRoster = $guildFinder->where('char_level', '>', '10')->fetch();

$sql = $guildRoster->getQuery();

\XF::dump($sql);

die();

We need to know what the query is that is being returned otherwise we can't begin to guess what the issue might be.

 

[Lukas W.]

How many rows does it grab without your where?

 

[Snog]

I am going to bet your field in the database is a varchar field and having the 10 wrapped in single quotes is giving unexpected results because it's treating the '10' as characters and not numeric values. If that's the case, try removing the single quotes and check what's returned then.

 

[Cupara]

getQuery literally gets the query, as a string, for debugging. Passing that into a foreach will have it fail.

Obviously you still have the issue. The steps posted above are to try and help you troubleshoot the issue.

$guildRoster = $guildFinder->where('char_level', '>', '10')->fetch();

$sql = $guildRoster->getQuery();

\XF::dump($sql);

die();

We need to know what the query is that is being returned otherwise we can't begin to guess what the issue might be.

Here is the output:

"""
\n
\t\t\tSELECT `xwow_roster`.*\n
\t\t\tFROM `xwow_roster`\n
\t\t\t\n
\t\t\tWHERE (`xwow_roster`.`char_level` > '10')\n
\t\t\t\n
\t\t
"""

How many rows does it grab without your where?

10 and it is the first 10 rows.

I am going to bet your field in the database is a varchar field and having the 10 wrapped in single quotes is giving unexpected results because it's treating the '10' as characters and not numeric values. If that's the case, try removing the single quotes and check what's returned then.

Nope, its INT with max length of 10

 

[Chris D]

What are the results of these queries if you run them directly on your database:

SELECT COUNT(*)
FROM xwow_roster

And:

SELECT COUNT(*)
FROM xwow_roster
WHERE char_level > '10'

 

[Cupara]

SELECT COUNT(*) FROM xwow_roster

The first one returns 26. The second one returns 25 as there is only 1 row that is below that level.

 

[Cupara]

Ok so apparently the issue is coming from my foreach statement.

        $db->beginTransaction();
       
        foreach($guildRoster AS $guild)
        {
            if (isset($guild['char_name']) == true)
            {
                try
                {
                    $results = $app->http()->client()->get($region.'/wow/character/'.$options->xenwow_realm.'/'.$guild['char_name'].'?fields=stats,items,professions&access_token='.$token);
                }
                catch (\GuzzleHttp\Exception\RequestException $e)
                {
                    if (null !== $e->getResponse())
                    {
                        $error = 'Error ' . $e->getResponse()->getStatusCode() . ' - ' . $e->getResponse()->getReasonPhrase();
                    }
                    else
                    {
                        $error = $e->getMessage();
                    }
       
                    return $error;
                }
               
                $character = \GuzzleHttp\json_decode($results->getBody(), true);
                $lastModified = $character['lastModified']/1000;
               
                if ($lastModified > $guild['char_lastModified'])
                {
                    $db->update('xwow_roster', [
                        'char_lastModified' => $lastModified
                    ]);
                }

                if ($character['class'] != $guild['char_class'])
                {
                    $db->update('xwow_roster', [
                        'char_class' => $character['class']
                    ]);
                }

                if ($character['race'] != $guild['char_race'])
                {
                    $db->update('xwow_roster', [
                        'char_race' => $character['race']
                    ]);
                }

                if ($character['level'] > $guild['char_level'] OR $character['achievementPoints'] > $guild['char_achieve'] OR $character['items']['averageItemLevelEquipped'] > $guild['char_gearscore'])
                {
                    $db->update('xwow_roster', [
                        'char_level' => $character['level'],
                        'char_achieve' => $character['achievementPoints'],
                        'char_gearscore' => $character['items']['averageItemLevelEquipped']
                    ]);
                }
            }
        }
       
        $db->commit();

EDIT: I'm trying this another way by separating each of those if condition into their own cron job.

 

[Lukas W.]

Your update queries are updating every single record in that table, as you don't have any where conditions. Either way, if you use the finder system, you shouldn't use $db->update anyway.

And to efficiently debug, use \XF::dump().

$guildRoster = $guildFinder->where('char_level', '>', '10')->fetch();
\XF::dump($guildRoster)

Should tell you quite quickly if the finder results are correct or not.

 

[Cupara]

Finder results are correct. Since the foreach is processing each row separately then the where condition I figured isn't necessary in the updating. I have changed them back to what I originally was using:

if ($lastModified > $guild['char_lastModified'])
                {
                    $lastModifiedResult = \XF::finder('GoblinTimes\xenWoW:Roster')->where('char_id', $guild['char_id'])->fetchOne();
                    $lastModifiedData = [
                        'char_lastModified' => $lastModified
                    ];
                    $lastModifiedResult->fastUpdate($lastModifiedData);
                }

Using fastUpdate() but still same results, only updating the first 10 rows of the table.

 

[Jake B.]

Have you confirmed that $guild['char_lastModified'] is less than the value of $lastModified?

 

[Cupara]

Have you confirmed that $guild['char_lastModified'] is less than the value of $lastModified?

Yes, I even added in:

OR $guild['lastModified'] = '0'

I even changed the field for lastModified to allow null and got rid of all the 0

 

[Cupara]

Thanks to some digging, I realized the issue and have it solved. Was more of my fault for not thinking.