XF 2.1 Not grabbing all results

[Cupara]

I'm using the finder to grab entries from the database. Problem is that no matter which table I'm grabbing, it only grabs 23 entries even though in one table there are 956 entries, in another table, there are over 200,000 entries. Is there anything I should check? Here is the code from one page for the index.

public function actionIndex()
    {
        $finder = \XF::finder('BetClever\Tipsters:Leagues');

        $page = $this->filterPage();
        $perPage = 10;

        $finder->limitByPage($page, $perPage);

        $filter = $this->filter('_xfFilter', [
            'text' => 'str',
            'prefix' => 'bool'
        ]);
        
        if (strlen($filter['text']))
        {
            $finder->whereOr([
                ['league_name', 'LIKE', $finder->escapeLike($filter['text'], $filter['prefix'] ? '?%' : '%?%')],
                ['Sports.name', 'LIKE', $finder->escapeLike($filter['text'], $filter['prefix'] ? '?%' : '%?%')],
                ['Countries.name', 'LIKE', $finder->escapeLike($filter['text'], $filter['prefix'] ? '?%' : '%?%')]
            ]);
        }

        $total = $finder->total();
        $leagues = $finder->with('Sports', 'Countries')->order('id', 'ASC')->fetch();
        
        $viewParams = [
            'leagues' => $leagues,
            'total' => $total,
            'page' => $page,
            'perPage' => $perPage,
            'filter' => $filter['text']
        ];
        return $this->view('BetClever\Tipsters:Leagues\Listing', 'bc_admin_leagues_list', $viewParams);
    }

 

[Snog]

You'll only get the number of entries that match in your Leagues table. The others are most likely "TO_ONE" relationships to that table. So, you'll never get more matches than your Leagues table can provide in a single query.

In other words, just adding a relationship doesn't fetch all entries from the other tables.

My usual disclaimer: I could be wrong.

 

[Cupara]

I was afraid of that.

If I use anything other than TO_ONE I get this error:

Exception: Joins only support TO_ONE relationships currently in src/XF/Mvc/Entity/Finder.php at line 774

I notice that only happens if I use TO_MANY in the main entity I'm calling but not in the others.

I'm not sure how to fix the issue. I have TO_MANY in the entities I'm calling in the With but still the results are the same.

 

[Snog]

Just briefly, TO_MANY doesn't work the way you think it does.

So far as I know, TO_MANY can only be called in a very specific way. Such as by an entity object $forum->Read[$user->user_id]. I haven't done anything with TO_MANY to be sure of that. It's just what I see in a quick look at XenForo itself.

My usual disclaimer: I could be wrong.

 

[Cupara]

@Snog Yeah I'm seeing that now. Because I have a table, Countries, that only has 23 entries then my results for Leagues will be limited to 23 instead of grabbing all entries and matching countries to them I guess I'm stuck.

 

[Snog]

Hmm, that's not entirely correct.

Because you're using a whereOr for each table, let's say you're searching for CA as your "text". But, none of your league_names have CA in them. Then none of the Sports.names have CA in them. Now the only thing left is the Countries.name that matches CA and you have 23 leagues that match that.

At least that's the way I'm reading your query.

 

[Cupara]

@Snog Ok then what would be the proper way for me to do this so it would load all entries and still be able to search for matches?

I removed the $filter stuff and the filter on the page works fine so that is interesting. Guess I don't need that extra code after all.

EDIT: After doing some editing of the conditions for entities, I removed the With() and all results display including the data I need from those other tables I have relations set for.

 

[Snog]

If you re-read what I said in my last post. You are most likely getting the results you asked for with the "text" you're searching (and if it starts with or is part of the search (prefix).

Without knowing what text you're searching for (and if it's a starts with or anywhere in the names) and what the entire result is, it's difficult to say if that query isn't working properly or not.

 

[Bespoke]

If you are stuck, you could always try skipping the finder and instead create your own SQL Statement Syntax to ensure you are getting the results you want.

 

[Cupara]

Yeah I thought of that. I got it figured out after reading some posts. As long as the relations exist it will grab the data without the use of With().

Thank you @Snog and @Bespoke for your help

 

[Bespoke]

As long as the relations exist it will grab the data without the use of With()

Not quite as far as I know:

Entities, finders, and repositories | Developer Documentation | XenForo

Developer documentation for XenForo

xenforo.com

Perhaps you are fetching what you want "on the fly" at a cost of a query?

 

[Cupara]

I'm not sure. I'll check debug info