Displaying a Datagrid with a related filed column

Grocery CRUD Community Edition
1

I have a base select query that I am trying to reproduce using GC functions. I am having an issue displaying the related field COLUMN because the match fields are not the primary key in the relationship.

Here is the query:

SELECT 
a.`ID_Account` as "Account #",
a.`Account` as "Account",
a.`Phone1` as "Phone",
a.`Email` as "Email",
c.`Name_First` as "First Name",
c.`Name_Last` as "Last Name",
a.`Account_Type` as "Account Type",
a.`Status` as "Status"

FROM `fsc_ACC_Accounts` a

LEFT JOIN fsc_CON_Contacts c ON a.`ID_Account` = c.`id_account` AND c.`flag_primary` = 1

Can someone assist me? Thanks in advance.

2

setRelation - Grocery CRUD - A PHP CRUD Generator Library (and other setRelation*)
Would that help?

3

Hello @dwdc this is the 1-1 relation that I want to create a long time ago but there wasn’t anyone that was asking for it to be honest :slight_smile:

Currently the only work-around that I can think of is to use setModel - Grocery CRUD - A PHP CRUD Generator Library . More specifically your model will look like this:

<?php 

use GroceryCrud\Core\Model;
use Laminas\Db\Sql\Select;

class CustomAccountModel extends Model {

    /**
     * @param \Laminas\Db\Sql\Select $select
     * @return \Laminas\Db\Sql\Select
     */
    public function extraJoinStatements($select)
    {
        $select->join(
            ['c' => 'fsc_CON_Contacts'], // alias for the joined table
            'a.ID_Account = c.id_account AND c.flag_primary = 1', // join condition
            [
                'Name_First' => 'Name_First',
                'Name_Last' => 'Name_Last'
            ],
            Select::JOIN_LEFT
        );
        
        return $select;
    }

    /**
     * @param \Laminas\Db\Sql\Select $select
     * @return \Laminas\Db\Sql\Select
     */
    public function extraWhereStatements($select)
    {
        // Add any additional WHERE clauses if needed
        return $select;
    }
}

And then you can call the Name_First and Name_Last from your columns.

4

I ended up just adding a view in MySQL and then referencing that in GC. I have many complex SELECTS in my system that use Joins, Sub_Selects, Procedures, and Functions to view List data. Should creating Views be the norm when I need this functionality (easier)? Is there disadvantages to this?

Didn’t an earlier version of GC have a function where you can generate a datagrid off a MySQL Select string?

5

Hello @dwdc ,

Yes SQL VIEWS is the best and simpler way to get those data. There is not any disadvantage really. Just make sure that you index correctly the relation fields so you will not have heavy queries.

There wasn’t any version of Grocery CRUD handling the whole query but we had setModel always where you can handle custom Queries.

Regards
Johnny

1 Like