[st28]

Hi

Is there an example on how to use custom queries on Laravel Glossary Crud Enterprise. I have looked at set model but the examples seems to be more towards code igniter. Is there some example for Laravel. 

 

Basically I have complex select queries that needs to pull thru glossary crud. I don't want to create views or temp table if I don't have to.

 

Thanks a lot.

[st28]

Can someone please clarify this. I am trying to select custom columns within select. Can this work with Custom Model. I have tried and failed to get it to work. If someone can show some light, even if in code ignighter I can convert it to Laravel.

Many Thanks!

 

SELECT

e.id as id,
first_name,
last_name,
previous_surname,
also_known_as,
address1,
address2,
address3,
county,
postcode,
nationality,
NI,
place_of_birth,
home_telephone,
work_telephone,
mobile_personal,
mobile_work,
email_personal,
email_work,
employment_start_date,
employment_end_date,
currently_employed,
(SELECT p.title FROM
employees_posts AS ep
INNER JOIN posts AS p ON ( ep.post_id = p.id AND p.latest_revision = 1)
WHERE ep.employee_id = e.id AND ep.latest_revision = 1
ORDER BY ep.start_date DESC
LIMIT 0,1
) AS recent_post_title,
(SELECT p.team FROM
employees_posts AS ep
INNER JOIN posts AS p ON ( ep.post_id = p.id AND p.latest_revision = 1)
WHERE ep.employee_id = e.id AND ep.latest_revision = 1
ORDER BY ep.start_date DESC
LIMIT 0,1
) AS team,
(SELECT pl.post_location FROM
employees_posts AS ep
INNER JOIN posts AS p ON ( ep.post_id = p.id AND p.latest_revision = 1)
INNER JOIN post_locations pl ON p.post_location_id = pl.id
WHERE ep.employee_id = e.id AND ep.latest_revision = 1
ORDER BY ep.start_date DESC
LIMIT 0,1
) AS post_location,
(SELECT pc.post_company FROM
employees_posts AS ep
INNER JOIN posts AS p ON ( ep.post_id = p.id AND p.latest_revision = 1)
LEFT JOIN post_companies AS pc ON (p.post_company_id = pc.id )
WHERE ep.employee_id = e.id AND ep.latest_revision = 1
ORDER BY ep.start_date DESC
LIMIT 0,1
) AS recent_post_company,
(SELECT p.title FROM
employees_posts AS ep
INNER JOIN posts AS p ON ( ep.post_id = p.id AND p.latest_revision = 1)
WHERE ep.employee_id = e.id AND ep.latest_revision = 1
ORDER BY ep.start_date DESC
LIMIT 1,1
) AS title_2,
(SELECT pc.post_company FROM
employees_posts AS ep
INNER JOIN posts AS p ON ( ep.post_id = p.id AND p.latest_revision = 1)
LEFT JOIN post_companies AS pc ON (p.post_company_id = pc.id )
WHERE ep.employee_id = e.id AND ep.latest_revision = 1
ORDER BY ep.start_date DESC
LIMIT 1,1
) AS company_2,
(SELECT COUNT(*) FROM employees_posts WHERE employees_posts.employee_id = e.id AND latest_revision = 1) AS posts_total
FROM employees AS e
LEFT JOIN counties AS c ON (e.county_id = c.id)
LEFT JOIN dates AS d ON (e.id= d.employee_id)
LEFT JOIN nationalities AS n ON (e.nationality_id = n.id)
WHERE LENGTH(first_name) > 0

[st28]

To add to above, it seems the result comes in the custom model correctly but the rendered output and columns is only for the 'employees' table set in crud->setTable command. As if its not using the custom model result at all ?

[st28]

Ok, some more info which might shed some light.

When I use the line - $crud->columns(['column1', 'column2']); in the controller. The custom model query is never triggered. 

If I comment that line then the custom model query is triggered. 

 

Has anyone else seen this happening ?

Can you please help.

Thanks!