Hi all,
Can any one please provide me the solution for this problem?
I am not getting correct results from that query (may be I am doing in a
wrong way).
Pls help me,
- Murali
On Wed, Nov 11, 2009 at 2:21 PM, Murali Krishna B <
muralikrishna.vi...@gmail.com> wrote:
> Hi,
>
> I am looking at a bit complex query.
>
> From the query I need the following data:
>
> For a given Student, the total no of students in each course attended by
> him. (Many to many relationship between student and course).
>
> MySQL query is doing the same. It is collecting course_id's of courses
> attended by that student(in inner query). For those courses, it is giving
> count of students.
>
> Thanks,
>
> Murali
>
>
> On Wed, Nov 11, 2009 at 1:19 PM, Alexandru-Emil Lupu <gang.al...@gmail.com
> > wrote:
>
>> Hi!
>> First of all... i think you are doing it wrong ...
>>
>> i guess this " select(course_id as cid,count(*) from sample where
>> course_id in (select s.course_id from sample s where s.student_id = 1) group
>> by(student_id); "
>>
>> could be written as
>>
>> select course_id as cid,count(*) from sample s where s.student_id = 1;
>>
>> And the dql would be:
>>
>> $rows = Doctrine_Query::create()
>>
>> ->select('s.course_id as cid,count(*) as num')
>> ->from('Sample s')
>> ->where('s.student_id = ?', 1)
>> ->execute()
>> ->getData();
>>
>> what have you done is a little bit more complex (the sql thing)...
>>
>>
>> On Wed, Nov 11, 2009 at 9:20 AM, Murali Krishna B <
>> muralikrishna.vi...@gmail.com> wrote:
>>
>>> Hi all,
>>>
>>> I have a table 'sample' with two columns: course_id and student_id.
>>>
>>> It is a many-to-many relationship table between Course and Students.
>>>
>>> Now I am facing a problem with Doctrine_Query. I am able to run this
>>> query in mysql prompt.
>>>
>>> select(course_id as cid,count(*) from sample where course_id in (select
>>> s.course_id from sample s where s.student_id = 1) group by(student_id);
>>>
>>> To convert in DQL,
>>>
>>> $query = Doctrine_Query::create()
>>> ->select('s.course_id as cid,count(*) as num')
>>> ->from('Sample s')
>>> ->where('s.course_id IN (select t.course_id from Sample t
>>> where t.student_id = ?)',1)
>>> ->groupBy('s.course_id');
>>>
>>> $rows = $query->fetchArray();
>>>
>>>
>>> This Query is always returning a single row, though I am getting many
>>> rows from mysql query.
>>>
>>> --
>>> Thanks & Regards,
>>>
>>> Murali Krishna.B,
>>>
>>>
>>>
>>>
>>
>>
>> --
>> Hawe a nice day!
>> Alecs
>>
>> As programmers create bigger & better idiot proof programs, so the
>> universe creates bigger & better idiots!
>> I am on web: http://www.alecslupu.ro/
>> I am on twitter: http://twitter.com/alecslupu
>> I am on linkedIn: http://www.linkedin.com/in/alecslupu
>> Tel: (+4)0748.543.798
>>
>>
>> >>
>>
>
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"symfony users" group.
To post to this group, send email to symfony-users@googlegroups.com
To unsubscribe from this group, send email to
symfony-users+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/symfony-users?hl=en
-~----------~----~----~----~------~----~------~--~---
