Check if exist a record with parent_id of the referenced record (FAQ scenario))

Yii 2.0 General Discussions
1

I have a table with FAQ qa defined like this: id, parent_id, …, where parent_id can be null if the record is an question. When someone answer the question, another record is inserted in table (this time with parent_id set to the id of the question being answered).

How can I check for for unanswered questions?

// New questions count
$count = Qa::find()->where(['status' => 'new'])->count();`

// Unanswered questions count
$count = Qa::find()->where(['???'])->count();`
2

Try:

// Unanswered questions count
$count = Qa::find()->where(['parent_id' => NULL])->count();
3

Thanks for you reply, this is trivial, but I need to know to which row in the table is associated. For example:

If someone post a question having the ID 1, I need to check if this ID 1 is in the column PARENT_ID. If not the question is not answered. Somehow I have to compare between two rows in the table.

Example with unanswered question:
ID | PARENT_ID | TEXT
---+-----------+------------------------
_1 | NULL _____| Question 1?
---+-----------+---<--no more records---

Example with answered question:
ID | PARENT_ID | TEXT
---+-----------+------------------------
_3 | NULL _____| Question 3?
_4 | ___3 _____| Answer to question ID 3

A workaround will be to have two tables. One for questions, one for answers and a relation between. But I plan to upgrade to a “tree like” structure, for more answers (replies) on answers … once I know how to check for record reference in the same table.

Best regards Luka

4

I feel a bit puzzled.

It looks it is more code logic related task. You can just look for the already existing record when someone post the question (Are the questions somehow predefined? How the user knows the ID when posting?)

If you really need to compare rows you can point the same table two times in one query using aliases. Take a look: https://www.yiiframework.com/doc/guide/2.0/en/db-query-builder#from

5

This paragraph is quite confusing. “if someone post a question with ID 1” - like creating a new entry or searching for answers for already existing question?

Because if you mean creating a question, then of course there isn’t any answer, you don’t have to check it - of course, your foreign key for parent_id has to be properly set.
If you just searching for existing answers, it’s very simple -

Qa::find()->where([‘parent_id’ => 1])->count();

or

Qa::find()->where([‘parent_id’ => 1])->exist();

1 Like