Advanced SQL query in symfony2 doctrine

Question

I am working on a project using Symfony2.8 and MySQL. I have 4 tables in my database that are users, quotes, articles, articlesquotes.

user table

<html>
  <body>
    <table border="1">
      <tr>
        <th>user_id</th>
        <th>name</th>
        <th>age</th>
      </tr>
      <tr>
        <td>7</td>
        <td>Alex</td>
        <td>20</td>
      </tr>
      <tr>
        <td>8</td>
        <td>John</td>
        <td>30</td>
      </tr>
      
      </table>
    </body>
  </html>

quotes table

<html>
  <body>
    <table border="1">
      <tr>
      <th>quote_id</th>
      <th>user_id</th>
      <th>reference</th>
        </tr>
      <tr>
      <td>61</td>
      <td>7</td>
      <td>AE20</td>
        </tr>
      <tr>
      <td>62</td>
      <td>7</td>
      <td>AE21</td>
        </tr>
      <tr>
      <td>63</td>
      <td>7</td>
      <td>AE22</td>
        </tr>
      <tr>
      <td>64</td>
      <td>8</td>
      <td>AE29</td>
        </tr>
      
      </table>
    </body>
  </html>

articlesquote

<html>
  <body>
    <table border="1">
      <tr>
      <th>id</th>
      <th>quote_id</th>
      <th>article_id</th>
        <th>qte</th>
        </tr>
      <tr>
        <td>58</td>
        <td>61</td>
        <td>2</td>
        <td>7</td>
       </tr>
      <tr>
        <td>59</td>
        <td>62</td>
        <td>3</td>
        <td>8</td>
       </tr>
      <tr>
        <td>60</td>
        <td>63</td>
        <td>1</td>
        <td>9</td>
       </tr>
       <tr>
        <td>61</td>
        <td>63</td>
        <td>2</td>
        <td>10</td>
       </tr>
      
      </table>
    </body>
  </html>

articles table

<html>
  <body>
    <table border="1">
      <tr>
        <th>article_id</th>
        <th>name</th>
      </tr>
      <tr>
        <td>1</td>
        <td>article1</td>
      </tr>
      <tr>
        <td>2</td>
        <td>article2</td>
      </tr>
      <tr>
        <td>3</td>
        <td>article3</td>
      </tr>
      
      </table>
    </body>
  </html>

now the relation between these tables is

user<--One-To-Many-->quote<--one-To-Many-->ArticlesQuote<--Many-To-One-->Article

I would like to get articles that are in all quotes of user number 7 ordered by quote_id

I would like to get articles that are in quote number 63 of the user number 7

thank you for helping me.

score 0 · Accepted Answer · answered Jul 29 '16 at 11:49

0

try this:

Select a.* From articles a 
Inner Join
    articlesquote aq On a.article_id=aq.article_id 
Inner Join
    quotes q On q.quote_id=aq.quote_id 
Inner Join
    user u On u.user_id=q.user_id
Where
    u.user_id=7 and q.quote_id=63

answered Jul 29 '16 at 11:49

jonju

2,711
1
13
19

thank you it works!!! what about the first one : I would like to get articles that are in all quotes of user number 7 ordered by quote_id – Mostafa Jul 29 '16 at 14:11
replace 'and q.quote_id=63' with 'order by q.quote_id'. that should do it. – jonju Jul 29 '16 at 14:14

Advanced SQL query in symfony2 doctrine

1 Answers1