Where clause in Editor for Compound Primary Key

Where clause in Editor for Compound Primary Key

icefieldicefield Posts: 45Questions: 19Answers: 1

For the Editor example showing how to use a compound primary key here, how would one go about adding a where-clause to limit the records shown in the table? That is, in your example, what would the where-clause look like to limit the display to only one person's visits?

For my case, I have a link table with the following structure:

CREATE TABLE `MapLocationRelationship` (
  `_idMap`            INTEGER NOT NULL,
  `_idLocation`       INTEGER NOT NULL,
  `_idEvent`          INTEGER NOT NULL,
  PRIMARY KEY ( `_idMap`, `_idLocation` )

I've tried to use the following where-clause

...
            ->where(function ($q) use ($idMap) {
                $q->where('MapLocationRelationship._idMap', $idMap);
            })
...

but am getting an exception: Call to member function where() on array.

Answers

  • icefieldicefield Posts: 45Questions: 19Answers: 1

    Ah yes, figured this out. Two things were required for me to get this working:

    1) Initially only had leftJoin to Location table. Added a second one to Map table.

    2) Added a where-clause (as shown in this question) to the Field::inst('MapLocationRelationship._idMap') field as follows:

                    Field::inst( 'MapLocationRelationship._idMap' )
                        ->setValue( $idMap )
                        ->options( Options::inst()
                            ->table('Map' )
                            ->value('_id')
                            ->label('name')
                            ->where( function($r) use ($idMap) {
                                $r->where('_id', $idMap );
                            })
                        )
                        ->validator( Validate::dbValues()),
    
    

    Seems to be working as desired for me now.

This discussion has been closed.