1,105,375 Community Members

I need help in count query in rawsql

Member Avatar
Vijaysurya
Light Poster
35 posts since Jan 2012
Reputation Points: -2 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

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.

Member Avatar
urtrivedi
Posting Virtuoso
1,900 posts since Dec 2008
Reputation Points: 249 [?]
Q&As Helped to Solve: 409 [?]
Skill Endorsements: 27 [?]
 
0
 

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

Member Avatar
Vijaysurya
Light Poster
35 posts since Jan 2012
Reputation Points: -2 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

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 }
Member Avatar
urtrivedi
Posting Virtuoso
1,900 posts since Dec 2008
Reputation Points: 249 [?]
Q&As Helped to Solve: 409 [?]
Skill Endorsements: 27 [?]
 
0
 

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
You
This article has been dead for over three months: Start a new discussion instead
Post:
Start New Discussion
Tags Related to this Article