I need help in count query in rawsql

[Vijaysurya]

Hai everybody,

I need help in doctrine raw sql query,

this query count not properly,

$query = $db->prepare("SELECT count(p.friend_id) AS count , p.layer_id, l. *
                                        from layer l, people p
                                        where l.personal_id = :personal_id
                                        and l.id = p.layer_id


        ");







layer_id           friend_id
5                     2
6                     7
5                       3

this is my people table structure,

this query display result  count 2 in layer_id 6.

my expected result is

layer_id  count
5             2

 6            1

anybody plz help me.

[urtrivedi]

what is structure and relation of poeple with layer table (how layer related to people)

[Vijaysurya]

HAI,

here,i posted my table

Layer:
  columns:
    personal_id:     { type: integer, notnull: true }
    name:            { type: string(255), notnull: true }
    picture_id:      { type: integer, primary: true }
    privacy_setting: { type: string(64), notnull: true }
    view_notifications_in_this_layer: { type: integer, notnull: true }
  relations:
    Personal: { local: personal_id, foreign: id, onDelete: CASCADE }


People:
  options:
    symfony: { form: false, filter: false }
  columns:
    layer_id:  { type: integer, primary: true }
    friend_id: { type: integer, primary: true }
  relations:
    Layer:  { local: layer_id, foreign: id, onDelete: CASCADE }
    Friend: { class: Personal, local: friend_id, foreign: id, onDelete: CASCADE }

[urtrivedi]

if I see expected result I guess no need to join tables, you can simple do following query with only layer table

select layer_id, count(*) count from layer group by layer_id