No result for Doctrine query

[pali]

I have the following tables:

Code: Select all

Post:
  actAs: { Timestampable: ~ }
  columns:
    id: { type: integer(4), autoincrement: true, primary: true }
    title: { type: string(255), notnull: true }
    is_public: { type: boolean, notnull: true, default: 0 }
    category_id: { type: integer(4), notnull: true, default: 0 }
    subcategory_id: { type: integer(4), notnull: true, default: 0 }
    language_id: { type: integer(4), notnull: true }
    author_id: { type: integer(4), notnull: true }
    content: { type: clob }
  relations:
    Authors:
      class: Writer
      onDelete: CASCADE
      local: author_id
      foreign: id
      alias: Authors
    Languages:
      class: Language
      onDelete: CASCADE
      local: language_id
      foreign: id
      alias: Languages
    Keywords:
      class: Keyword
      refClass: PostHasKeyword
      local: post_id
      foreign: keyword_id
      alias: Keywords
    Categories:
      class: Category
      onDelete: CASCADE
      local: category_id
      foreign: id
      alias: Categories
    SubCategories:
      class: SubCategory
      onDelete: CASCADE
      local: subcategory_id
      foreign: id
      alias: Subcategories


PostHasKeyword:
  columns:
    id: {  type: integer(4), autoincrement: true, primary: true }
    post_id: { type: integer(4), notnull: true, primary: true }
    keyword_id: { type: integer(4), notnull: true, primary: true }
  relations:
    Post:
      local: post_id
      foreign: id
    Keyword:
      local: keyword_id
      foreign: id


Keyword:
  columns:
    id: { type: integer(4), autoincrement: true, primary: true }
    keyword: { type: string(255), notnull: true, unique: true }
    counter: { type: integer(4), default: 0 }


I am using the following Doctrine query:

Code: Select all

$q2 = Doctrine_Query::create()
  ->from(PostHasKeyword c')
  ->leftJoin('c.Post p')
  ->where('c.post_id=?',$postId);

where $postId gives the id of the inspected post.

Using the "$q2->getSQLQuery()" the generated SQL qouery seems below:

Code: Select all

SELECT
    d.id AS d__id,
    d.post_id AS d__post_id,
    d.keyword_id AS d__keyword_id,
    d2.id AS d2__id,
    d2.title AS d2__title,
    d2.is_public AS d2__is_public,
    d2.category_id AS d2__category_id,
    d2.subcategory_id AS d2__subcategory_id,
    d2.language_id AS d2__language_id,
    d2.author_id AS d2__author_id,
    d2.content AS d2__content,
    d2.created_at AS d2__created_at,
    d2.updated_at AS d2__updated_at
FROM
    post_has_keyword d
LEFT JOIN
    post d2 ON d.post_id = d2.id
WHERE (d.post_id = 1)

If I try the generated query via the phpMyAdmin it works perfect, gives back the expected results however it is look like an empty query when I use it in my PHP code.

How could it be?

Thank ouy in advance.

[danielh]

What are the next lines of code after this?

Code: Select all

$q2 = Doctrine_Query::create()
  ->from(PostHasKeyword c')
  ->leftJoin('c.Post p')
  ->where('c.post_id=?',$postId);

As a quick way to debug, try adding the following debug echos one at a time and see what the output is, they might give some clues:

Code: Select all

$result = $q2->execute();
echo gettype($result);
echo var_export($result, true);


[pali]

Code: Select all

$res = q2->execute();
foreach($res as $key => $value )
{
...
}

[pali]

I still cannot solve it...
However, as I suspect that during the processForm event, all of related records are locked or inaccessible.
And perhaps that could be root cause...

I edited a post which ID was 1 and I did the following two tests:
1) prepare a query with ID=1

Code: Select all

Doctrine_Query::create()
->from('PostHasKeyword k')
->where('k.post_id=1');

2) prepare a query with ID=2

Code: Select all

Doctrine_Query::create()
->from('PostHasKeyword k')
->where('k.post_id=2');

During the first test the result was 0 (!) but in the second case it gives the right result according to the stored database records.

If it is true I would like to know what is the best-practice in this case? How could I keep my keywords up-to-date?

[pali]

I am still in investigation...

I try to figure out where get into invisible my record.
Using a Netbeans 7.1.2 with xDebug I can see that my related record disappear after that the $con->commit() is called from function "save" of the sfFormObject.class.php however the edited record (effectively modified) is still query-able.

So my connection-table losts the related data...