How search in relation many to many in Doctrine?

User:
  columns:
    id:
      type: integer(4)
      autoincrement: true
      primary: true
    username:
      type: string(255)

Group:
  tableName: group_table
  columns:
    id:
      type: integer(4)
      autoincrement: true
      primary: true
    name:
      type: string(255)
  relations:
    Users:
      foreignAlias: Groups
      class: User
      refClass: GroupUser

GroupUser:
  columns:
    group_id:
      type: integer(4)
      primary: true
    user_id:
      type: integer(4)
      primary: true
  relations:
    Group:
      开发者_开发百科foreignAlias: GroupUsers
    User:
      foreignAlias: GroupUsers


DB:
USER:
id | username
1  | john
2  | kate
3  | alan

GROUP:
id  | name
1   | admin
2   | mod
3   | kate (!)

USERGROUP:

id_user | id_group
1       | 1
2       | 1
1       | 2
3       | 3
3       | 2
2       | 3

I would like make search system. I will search for example word: "KATE". How can i search in many to many table for KATE?

In input search i write "KATE". I must use WHERE LIKE in Doctrine. How this query must look? This should show me all user with username Kate and all user for group Kate.

Your DQL ammounts to the following...

Search for Users named kate or Users in the Group kate, returning user and groups

FROM User u LEFT JOIN u.Groups g WHERE u.username LIKE 'KATE' OR g.name LIKE 'KATE'

so...

$qry = Doctrine_Query::create()
  ->from('User u')
  ->leftJoin("u.Groups g")
  ->where("u.username LIKE ? OR g.name LIKE ?", array('KATE','KATE'));