Skip to main content

This site requires you to update your browser. Your browsing experience maybe affected by not having the most up to date version.

Form Questions /

Moderators: martimiz, Sean, Ed, biapar, Willr, Ingo, swaiba

Search - My DataObject has fields that are also DataObjects

Go to End

2 Posts   924 Views


Community Member, 2 Posts

15 December 2010 at 11:15am

I am new to SS... and I am developing a small application where I have this couple of objects:

class SBJob extends DataObject {

    static $singular_name = 'Job';
    static $plural_name = 'Jobs';

    static $db = array(
        'Title' => 'Text',
        'Description' => 'Text',
        'Location' => 'Text',
        'ExpiryDate' => 'Date',

    static $has_one = array('JobCategory' => 'SBJobCategory', 'JobType' => 'SBJobType');
    static $searchable_fields = array('Title', 'Description', 'Location', 'JobType.Title', 'JobCategory.Title');


where SBJobCategory, SBJobType are also extending DataObjects and I can define them in the backend.

What I'm trying to build is a search form that has these fields to search by:

Title - textField
Description - textField
Location - textField
JobCategory - DropDown, prefilled with the values already defined in the backend
JobType - DropDown, prefilled with the values already defined in the backend

I have the textfields on the page, but I am stuck woth dropdowns...
can u point me to some exmples or docs to read about this?

thank you.


Community Member, 2 Posts

16 December 2010 at 5:51am

I have added the 2 dropdowns with the needed values,
I created 2 new fields and added them to the SearchContext.

        $jobTypes = self::getJobTypes();
        if ($jobTypes) {
            $jobTypesMap = array_combine($jobTypes, $jobTypes);
        } else {
            $jobTypesMap = null;
        $jobTypeField = new DropdownField('JobType__Title', 'Job Type', $jobTypesMap);

        $jobCategories = self::getJobCategories();
        if ($jobCategories) {
            $jobCategoriesMap = array_combine($jobCategories, $jobCategories);
        } else {
            $jobCategoriesMap = null;
        $jobCategoryField = new DropdownField('JobCategory__Title', 'Job Category', $jobCategoriesMap);


now my problem is different

if I am searching using one of these 2 dropdown, everythign works

the PROBLEM is when I use BOTH dropdowns, as I can see the generated SQL QUERY is wrong

any idea how to adjust the generated SQL query, looks like it JOINS the SiteTree just once, and since I am left joining 2 tables,
1) SBJobCategory
2) SBJobType

I suppose SiteTree
should also be INNER JOINED twise with 2 diferent aliases...

right now I have just one alias used for both conditions which is wrong
and the INNER JOIN for the SBJobCategory comes Before LEFT JOIN, it should come After.

Couldn't run query: SELECT DISTINCT "SBJob"."ClassName", "SBJob"."Created", "SBJob"."LastEdited", "SBJob"."Title", "SBJob"."Description", "SBJob"."Location", "SBJob"."ExpiryDate", "SBJob"."JobCategoryID", "SBJob"."JobTypeID", "SBJob"."ID", CASE WHEN "SBJob"."ClassName" IS NOT NULL THEN "SBJob"."ClassName" ELSE 'SBJob' END AS "RecordClassName" 
FROM "SBJob" LEFT JOIN "SBJobType" AS "SBJobType" ON "SBJobType"."ID" = "SBJob"."JobTypeID" 
INNER JOIN "SiteTree" AS "SiteTree" ON "SBJobCategory"."ID" = "SiteTree"."ID" 
LEFT JOIN "SBJobCategory" AS "SBJobCategory" ON "SBJobCategory"."ID" = "SBJob"."JobCategoryID" 
WHERE ("SiteTree"."Title" LIKE '%Full Time%') AND ("SiteTree"."Title" = 'Administrative Management') 

Unknown column 'SBJobCategory.ID' in 'on clause'

Thank you.