selecting a column in join table doctrine 2

Question

I am not sure is the title is true. I am so sorry about that. But i think you'll understand my question.

I'm trying to select two column from my tables. I have two table and these have relationship.

First table's name is : Admins ( a few column -> username and bla bla bla)

Second table's name is: Auths (2 column -> id and name)

(they have a relationship like that -> Admins.auth - Auths.id )

And i have to select Admins.username and Auths.name.

I tried this code

$repository = $this->getDoctrine()
->getRepository('dbBundle:Admins');

$query = $repository->createQueryBuilder('a')
->addSelect('a.username')
->addSelect('au.name')
->leftJoin('a.auth','au','WITH','a.username=:username')
->setParameter('username', $userName)
->getQuery();

$products = $query->getResult();

but its not working.

However, this code is working fine.

$repository = $this->getDoctrine()
->getRepository('dbBundle:Admins');

$query = $repository->createQueryBuilder('a')
->addSelect('a.username')
->addSelect('au.name')
->leftJoin('a.auth','au')
->getQuery();

$products = $query->getResult();

---

Problem Solved: First of all my main problem was wrong parameter name, i mean get the parameter like that

$userName = $request->query->get('username');

but i post it like userName so its wrong because there is a case sensitive.

After i solved that there were 2 rows at results but it had to be just one.

So after this point i use innerJoin as @157 said.

And this my final code its working...

$repository = $this->getDoctrine()
    ->getRepository('dbBundle:Admins');

$query = $repository->createQueryBuilder('a')
    ->addSelect('a.username')
    ->addSelect('au.name')
    ->innerJoin('a.auth','au','WITH','a.username=:username')
    ->setParameter('username', $userName)
    ->getQuery();

Answer 1

Engin,

You need to perform an inner Join.

This is how your query should be structured in SQL:

SELECT Admins.username, Auths.id, Auths.name
FROM Admins
INNER JOIN Auths
ON Admins.username=Auths.name

Based off this, this is how your php should look:

<?php
  $con = mysql_connect("localhost","root","root");
  if (!$con) {
  die('Could not connect: ' . mysql_error());
  }

  mysql_select_db("test", $con);
  $result = mysql_query("SELECT Admins.username, Auths.id, Auths.name FROM Admins INNER JOIN Auths ON Admins.username=Auths.name");
  echo "<table border='1'>
  <tr>
  <th>Username</th>
  <th>ID</th>
  <th>Name</th>
  </tr>";
  while ($row = mysql_fetch_array($result)) {
  echo "<tr>";
  echo "<td>" . $row['username'] . "</td>";
  echo "<td>" . $row['id'] . "</td>";
  echo "<td>" . $row['name'] . "</td>";
  echo "</tr>";
  }
  echo "</table>";

  mysql_close($con);
?>

The following is how you would construct the DQL query in php:

  Doctrine_Query::create()
    ->select('a.username, au.id, au.name')
    ->from('Admins a')
    ->innerJoin('a.Auths au');

 echo $q->getSqlQuery();

The DQL: Doctrine Query Language, is a great resource as well!

Please let me know if you have any questions!