Questions: Ambiguous Column when using *_match with left join and allowSearch CMSB 3.75

4 posts by 2 authors in: Forums > CMS Builder
Last Post: January 13 (RSS)

I have a left join with

'allowSearch' = true

and get an ambiguous column on _match.

I need to specify the table name in my query string, so I tried 'event.catefory_match=3'; however, $_GET and $_REQUEST both convert the dot to an underscore.

What syntax does ZenDB expect when trying to table qualify *_match?

Jeff Shields
yaadev.com

By Dave - January 3

Hi Jeff,

If you can tell me the two table names, the column you want to join on, and what fields you want to search on, I can write some ZenDB code for you.

Dave Edis - Senior Developer
interactivetools.com

By kitsguru - January 7

I was using getRecords.

list($biosRecords, $biosMetaData) = getRecords(
    array(
        'tableName'   => "bios",
        'loadUploads' => true,
        'where'       => $where,
        'orderBy'     => $orderby,
        'allowSearch' => true,
        'perPage'     => 10,
        'leftJoin'    => array(
            'locations' => 'company'
        ),
        'limit'       => '1',
        'requireSearchMatch' => false,
        'debugSql'  => false,
    )
);

I was using a query string of ?category_match=13. This seemed to work until CMSB 3.75. Both tables have a category column, which is where I got the ambiguous column name.

I found I could use bios_category_match, and it worked. However, I see the value gets set in the $_REQUEST to 'bios.category_match'. If this is submitted, it is changed. PHP replaces the dot with an underscore by default.

I am a bit confused on the proper way to use _match.

Jeff Shields
yaadev.com

By Dave - January 13

Hi Jeff,

Can you try this (untested) code?

$query = "SELECT * FROM ::bios b
          LEFT JOIN ::locations l ON b.company = l.num
          WHERE b.category = :category
          ORDER BY b.num DESC
          LIMIT 1";
$results = DB::query($query, [
   ':category' => $_REQUEST['category_match'] ?? 0,
]);

showme($results);

The :: inserts the table prefix. Feel free to adjust or rename any fields as needed.

Let me know if it works for you or any questions.

Dave Edis - Senior Developer
interactivetools.com