Question: I have two tables:

* trips: id\_trip, id\_object, trip\_date, delta (8980026 rows)
* ranges: id\_range, id\_object, date\_since, date\_until (18490 rows)

I need to optimize the following select statement

```
  select r.id_range, sum(t.delta) sum_deltas
    from trips t,
         ranges r
   where t.id_object = r.id_object
     and t.trip_date between r.date_since and r.date_until
group by r.id_range

```

according to the condition there is always exactly one matching row for trip in 'ranges'

* the trips table is constantly growing, but there are no updates or deletions
* table ranges may change from time to time in any way (deletions, updates, inserts), so function based index is not the way :(
* there are indexes on id\_object (in both tables) and date\_since (in trips)

Does anyone have an idea how to speed things up, is it even possible?

Answer: 
Since 2.5.0 doctrine queryBuilder had its getAllAliases() function made public. So you would basically simply get the array from :

```
$aliases = $builder->getAllAliases();  

```

And retrieve an array like :

```
Array(
    [0] => p
    [1] => s
)

```
You can retrive the `select` part of your QueryBuilder by calling the `getDqlPart('select')` method.

More information [here](https://github.com/doctrine/doctrine2/blob/master/lib/Doctrine/ORM/QueryBuilder.php#L1028).

Then, look how doctrine does to parse select part [here](https://github.com/doctrine/doctrine2/blob/master/lib/Doctrine/ORM/QueryBuilder.php#L1072).

You can probably do the same to know if the table associated to your repository is called and what is its alias.