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
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?
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.