Propel and symfony : Join Table doesn't work

Question

Firstly I explain my issue :

I have two tables Job and JobCategory :

job:
   -------------------------------------------------
   | id   | category_job_id | job_name  | keywords  |
   -------------------------------------------------

JobCategory: 
  -------------------------
  | id   | categoty_name  |
  -------------------------

tho two tables is related by foreign key "category_job_id".

In this application I'm using Propel ORM. I hoped to make search using three fields keywords, job_name and category_name.

The first field is keywords is "input" who I can write keywords, the second field is Category_name is a "select", list of category. The third field is Job_name and is a "select", list of Job name, and if is not empty the keywords fields will be ignored.

I make function of search like this, but it doesn't work for me:

  public function searchFilter($job,$category,$keyword)
   {

$order = isset($this->order) ? $this->order : Criteria::ASC;

$job = '%' .$job. '%';
$category = '%' .$category. '%';

$c = new Criteria();

$c->addJoin(JobPeer::CATEGORY_JOB_ID, JobCategoryPeer::ID);
if((null !== $category) AND ($category !== ""))
{
 $c->addOr(JobCategoryPeer::CATEGORY_NAME,$category, Criteria::LIKE);    
}
if((null !== $job) AND ($job !== ""))
{
 $c->addOr(JobPeer::JOB_NAME,$job, Criteria::LIKE);   
}

$query = JobQuery::create(null, $c)
        ->joinWith('Job.JobCategory')
        ->orderByDateOfJob($order);

  if((null !== $keyword) AND ($keyword !== "")){
    $keyword = '%' .$keyword. '%';
    $query->filterByKeywords($keyword, Criteria::LIKE);
  }      

$results = $query->find();


return $results;

}

But the search is all cases is wrong!

I don't think it is the solution to your question, but there's a few mistakes in your post. Your table is likely to be job_category, not JobCategory - the latter is your corresponding model class. I should think categoty_name should be category_name too. — halfer
Have a look at the Propel docs btw: there's a toString method (or similar) you can apply to $query, which will give you the SQL it has generated. This is invaluable for debugging. — halfer

Jordan Kasper Jordan Kasper · Accepted Answer · 2013-04-21T17:42:36

I think something like this will work. If not, you can get the generated SQL before issuing the find() (see below) so that you (and we) can see what the output might be.

public function searchFilter($job,$category,$keyword)
{

  $order = isset($this->order) ? $this->order : Criteria::ASC;

  $query = JobQuery::create()->joinWith('JobCategory');
  $conditions = array();

  if((null !== $category) AND ($category !== ""))
  {
    $query->condition('catName', "JobCategory.CategoryName LIKE ?", "%$category%");
    $conditions[] = 'catName';
  }
  if((null !== $job) AND ($job !== ""))
  {
    $query->condition('jobName', "Job.JobName LIKE ?", "%$job%");
    $conditions[] = 'jobName';
  }
  if (sizeOf($conditions) > 1)
  {
     // join your conditions with an "or" if there are multiple
    $query->combine($conditions, Criteria::LOGICAL_OR, 'allConditions');
    // redefine this so we have the combined conditions
    $conditions = array('allConditions');
  }

  // add all conditions to query (might only be 1)
  $query->where($conditions);

  if((null !== $keyword) AND ($keyword !== ""))
  {
    $query->filterByKeywords("%$keyword%", Criteria::LIKE);
  }

  $query->orderByDateOfJob($order);
  $sql = $query->toString(); // log this value so we can see the SQL if there is a problem
  return $query->find();
}

Propel and symfony : Join Table doesn't work

1 Answers