• This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn more.

XF 2.0 Using finder to replace a query that has an inner join

AndyB

Well-known member
#1
I have the following query which works fine:

PHP:
$attendees = $db->fetchAll("
SELECT xf_andy_rsvp_register.user_id,
xf_andy_rsvp_register.thread_id,
xf_andy_rsvp_register.guest_count,
xf_andy_rsvp_register.note,
xf_user.username
FROM xf_andy_rsvp_register
INNER JOIN xf_user ON xf_user.user_id = xf_andy_rsvp_register.user_id
WHERE xf_andy_rsvp_register.thread_id = ?
ORDER BY xf_andy_rsvp_register.rsvp_register_id ASC
", $threadId);

I would like to use a finder to replace it.

My Entity

PHP:
<?php

namespace Andy\Rsvp\Entity;

use XF\Mvc\Entity\Entity;
use XF\Mvc\Entity\Structure;

class RsvpRegister extends Entity
{
    public static function getStructure(Structure $structure)
    {
        $structure->table = 'xf_andy_rsvp_register';
        $structure->shortName = 'Andy\Rsvp:RsvpRegister';
        $structure->primaryKey = 'rsvp_register_id';
        $structure->columns = [
            'rsvp_register_id' => ['type' => self::UINT, 'autoIncrement' => true, 'nullable' => true],
            'user_id' => ['type' => self::UINT, 'required' => true],
            'thread_id' => ['type' => self::UINT, 'required' => true],
            'guest_count' => ['type' => self::STR, 'maxLength' => 10, 'default' => ''],
            'note' => ['type' => self::STR, 'default' => ''],
        ];
        $structure->getters = [];
        $structure->relations = [
            'User' => [
                'entity' => 'XF:User',
                'type' => self::TO_ONE,
                'conditions' => 'user_id',
                'primary' => true
            ]
        ];       

        return $structure;
    }   
}

In my controller I have the following PHP code:

PHP:
$finder = \XF::finder('Andy\Rsvp:RsvpRegister');
$attendees = $finder
    ->where('thread_id', $threadId)
    ->with('XF:User')
    ->order('rsvp_register_id', 'ASC')->fetch();

I'm getting the following error:

Code:
LogicException: Unknown relation XF:User accessed on xf_andy_rsvp_register in src/XF/Mvc/Entity/Finder.php at line 630
[LIST=1]
[*]XF\Mvc\Entity\Finder->join() in src/XF/Mvc/Entity/Finder.php at line 572
[*]XF\Mvc\Entity\Finder->with() in src/addons/Andy/Rsvp/XF/Pub/Controller/Thread.php at line 109
[*]Andy\Rsvp\XF\Pub\Controller\Thread->actionIndex() in src/XF/Mvc/Dispatcher.php at line 249
[*]XF\Mvc\Dispatcher->dispatchClass() in src/XF/Mvc/Dispatcher.php at line 88
[*]XF\Mvc\Dispatcher->dispatchLoop() in src/XF/Mvc/Dispatcher.php at line 41
[*]XF\Mvc\Dispatcher->run() in src/XF/App.php at line 1844
[*]XF\App->run() in src/XF.php at line 328
[*]XF::runApp() in index.php at line 13
[/LIST]

What am I doing wrong?

Thank you.
 

AndyB

Well-known member
#3
Thank you, Chris. No more error message. What do I need to do to include the username in the $attendees object?

1509814310482.png
 

Chris D

XenForo developer
Staff member
#4
The attendees is now a collection (like an array) of attendee entities.

If you are looping through that collection (in a template for example) then each attendee entity can now access the username:

HTML:
<xf:foreach loop="$attendees" value="$attendee">
    {$attendee.User.username}
</xf:foreach>
 

AndyB

Well-known member
#5
I added the following to my template:

HTML:
<xf:foreach loop="$attendees" value="$attendee">
    {$attendee.User.username}
</xf:foreach>

but I get the following error when viewing a thread.

Code:
Template Compilation Error
public:andy_rsvp - Entity User (class: User) could not be found in /home/southbay/public_html/forums20b7/src/XF/Mvc/Entity/Manager.php:51
 

Chris D

XenForo developer
Staff member
#6
Did you change this:
PHP:
        $structure->relations = [
            'User' => [
                'entity' => 'XF:User',
                'type' => self::TO_ONE,
                'conditions' => 'user_id',
                'primary' => true
            ]
        ];
To:
PHP:
        $structure->relations = [
            'User' => [
                'entity' => 'User',
                'type' => self::TO_ONE,
                'conditions' => 'user_id',
                'primary' => true
            ]
        ];
If so, that's wrong. The 'entity' class should still be XF:User.
 

AndyB

Well-known member
#8
In the same add-on I'm now trying to replace this query:

PHP:
$db->query("
    INSERT INTO xf_andy_rsvp
        (thread_id, event_date, deadline, maximum)
    VALUES
        (?,?,?,?)
", array($threadId, $eventDate, $deadline, $maximum));

I tried this but it's not working:

PHP:
$data = [
    'thread_id' => $threadId,
    'event_date' => $eventDate,
    'deadline' => $deadline,
    'maximum' => $maximum
];

$rsvp->fastUpdate($data);

I get the following error:

HTML:
ErrorException: [E_NOTICE] Undefined variable: rsvp in src/addons/Andy/Rsvp/XF/Pub/Controller/Thread.php at line 234

[LIST=1]
[*]XF::handlePhpError() in src/addons/Andy/Rsvp/XF/Pub/Controller/Thread.php at line 234
[*]Andy\Rsvp\XF\Pub\Controller\Thread->actionRsvpCreateSave() in src/XF/Mvc/Dispatcher.php at line 249
[*]XF\Mvc\Dispatcher->dispatchClass() in src/XF/Mvc/Dispatcher.php at line 88
[*]XF\Mvc\Dispatcher->dispatchLoop() in src/XF/Mvc/Dispatcher.php at line 41
[*]XF\Mvc\Dispatcher->run() in src/XF/App.php at line 1844
[*]XF\App->run() in src/XF.php at line 328
[*]XF::runApp() in index.php at line 13
[/LIST]
 

AndyB

Well-known member
#10
Got it!

So the MySQL query:

PHP:
$db->query("
    INSERT INTO xf_andy_rsvp
        (thread_id, event_date, deadline, maximum)
    VALUES
        (?,?,?,?)
", array($threadId, $eventDate, $deadline, $maximum));

is replaced with this:

PHP:
$rsvp = $this->em()->create('Andy\Rsvp:Rsvp');
$rsvp->thread_id = $threadId;
$rsvp->event_date = $eventDate;
$rsvp->deadline = $deadline;
$rsvp->maximum = $maximum;
$rsvp->save();
 
Last edited:

AndyB

Well-known member
#11
This delete query:

PHP:
$db->query("
    DELETE FROM xf_andy_rsvp
    WHERE thread_id = ?
", $threadId);

is replaced with:

PHP:
$rsvp = $this->em()->find('Andy\Rsvp:Rsvp', $threadId);
$rsvp->delete();
 
Last edited:

AndyB

Well-known member
#12
This update query:

PHP:
$db->query("
UPDATE xf_andy_rsvp SET
    event_date = ?,
    deadline = ?,
    maximum = ?
    WHERE thread_id = ?
", array($eventDate, $deadline, $maximum, $threadId));

is replaced with:

PHP:
$rsvp = $this->em()->find('Andy\Rsvp:Rsvp', $threadId);

$data = [
    'event_date' => $eventDate,
    'deadline' => $deadline,
    'maximum' => $maximum
];

$rsvp->fastUpdate($data);
 

AndyB

Well-known member
#13
Delete a row where you need to match two fields:

PHP:
$finder = \XF::finder('Andy\Rsvp:RsvpRegister');
$attendee = $finder
    ->where('user_id', $userId)
    ->where('thread_id', $threadId)
    ->fetchOne();