Jump to:

2002 Posts in 1422 Topics by 616 members

Form Questions

SilverStripe Forums » Form Questions » Search - My DataObject has fields that are also DataObjects

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

Page: 1
Go to End
Author Topic: 645 Views
  • Deschanel
    Avatar
    Community Member
    2 Posts

    Search - My DataObject has fields that are also DataObjects Link to this post

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

  • Deschanel
    Avatar
    Community Member
    2 Posts

    Re: Search - My DataObject has fields that are also DataObjects Link to this post

    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);
    $jobTypeField->setHasEmptyDefault(true);

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

    ...
    $context->addField($jobTypeField);
    $context->addField($jobCategoryField);

    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')
    LIMIT 2 OFFSET 0

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

    Thank you.

    645 Views
Page: 1
Go to Top

Want to know more about the company that brought you SilverStripe? Then check out SilverStripe.com

Comments on this website? Please give feedback.