Potential bug

Can you try this?

$this->response[$this->type] = $this->modelsManager->createBuilder()
            ->from('Modules\Engine\Models\Artists\Bands\ArtistsBands')
            ->join('Modules\Engine\Models\PerformancesDates')
            ->andWhere('date = :date:', array('date' => $this->date))
            ->andWhere('genre = :genre:', array('genre' => $this->genre))
            ->limit(100)
            ->getQuery()
            ->execute();

Also something that came to mind, not sure but I think date is reserved so you could try enclosing it in brackets like so

->where('[date] = :date:", ["date" => $this->date])

Nope, absolutle same results ... Is it a bug, becouse if i write it with phql it works corectly: Controller:

    $ab = new ArtistsBands();
            $this->response[$this->type] = $ab->explore($this->genre, $this->date);

ArtistsBands Model :

public function explore($genre, $date)
{
    $option = '';
    $optionJoin = '';
    if (!empty($date)) {
        $optionAnd = " AND date = '$date'";
        $optionJoin = "INNER JOIN Modules\Engine\Models\PerformancesDates as pd ON ab.band_id = pd.owner_id";
    }

    $phql = "SELECT ab.* FROM Modules\Engine\Models\Artists\Bands\ArtistsBands as ab
    $optionJoin
    WHERE genre = '$genre' $optionAnd";

    return $this->getDi()->getModelsManager()->createQuery($phql)->execute();
}

Well in your SQL statement you use inner join while in the builder you use join. That is the only difference I can see between the two code snippets.

Like this:

$this->response[$this->type] = $this->modelsManager->createBuilder()
            ->addFrom('Modules\Engine\Models\Artists\Bands\ArtistsBands', 'ab')
            ->innerJoin('Modules\Engine\Models\PerformancesDates', 'ab.band_id = pd.owner_id', 'pd')
            ->where('date = :date:', array('date' => $this->date))
            ->andWhere('genre = :genre:', array('genre' => $this->genre))
            ->limit(100)
            ->getQuery()
            ->execute();

I think it works now , but i prefer to stay with phql, because there is not good the way of creating query with some parameters which not all of them are required.

     public function explore($location, $date)
{
    $optionAnd = '';
    $optionJoin = '';
    $optionWhere = '';

    if (!empty($date) && !empty($location)) {
        $optionJoin = "INNER JOIN Modules\Engine\Models\PerformancesDates as pd ON a.id = pd.owner_id";
        $optionWhere = "WHERE a.location = '$location'";
        $optionAnd = " AND pd.date = '$date'";
    } elseif (!empty($date) && empty($location)) {
        $optionJoin = "INNER JOIN Modules\Engine\Models\PerformancesDates as pd ON a.id = pd.owner_id";
        $optionWhere = "WHERE pd.date = '$date'";
    } elseif (!empty($location) && empty($date)) {
        $optionWhere = "WHERE a.location = '$location'";
    }

    $phql = "SELECT a.* FROM Modules\Engine\Models\Artists\Artists as a
    $optionJoin
    $optionWhere
    $optionAnd";

    return $this->getDi()->getModelsManager()->createQuery($phql)->execute();
}

If you are looking for conditional building this will be what you are trying to do above

public function explore($location, $date)
{
    $builder = $this->modelsManager->createBuilder();

    $builder->columns('a.*')
            ->addFrom('Modules\Engine\Models\Artists\Artist', 'a');

    if (!empty($date) && !empty($location)) {
        $builder->innerJoin('Modules\Engine\Models\PerformancesDates', 'a.id = pd.owner_id', 'pd')
                ->andWhere('a.location' = :location:', ['location' => $location])
                ->andWhere('pd.date' = :pddate:', ['pddate' => $date]);
    } elseif (!empty($date) && empty($location)) {
        $builder->innerJoin('Modules\Engine\Models\PerformancesDates', 'a.id = pd.owner_id', 'pd')
                ->andWhere('pd.date' = :pddate:', ['pddate' => $date]);
    } elseif (!empty($location) && empty($date)) {
        $builder->andWhere('a.location' = :location:', ['location' => $location]);
    }

    return $builder->getQuery()->execute();
}

If you have some time can you try outputting the SQL of the builder like so:

print_r($builder->getPhql());
die();

and compare it with the raw PHQL? If those really do not match then yes it is a bug and you can of course open an issue in the issue tracker.

The reason I seem sceptical is because I have used the builder heavily in a few applications and I never had issues with joins and such.

Phalcon version 1.3.4

This returns correct results:

SELECT a.* FROM [Modules\Engine\Models\Artists\Artist] AS [a] WHERE a.location = :location:

This returns scanning error:

SELECT a.* FROM [Modules\Engine\Models\Artists\Artists] AS [a] INNER JOIN [Modules\Engine\Models\PerformancesDates ] AS [pd ] ON a.id = pd.owner_id WHERE pd.date = :date:

    Scanning error before 'Modules\Engine\M...' when parsing: SELECT a.* FROM [Modules\Engine\Models\Artists\Artists] AS [a] INNER JOIN [Modules\Engine\Models\PerformancesDates ] AS [pd ] ON a.id = pd.owner_id WHERE pd.date = :date: (170)

This returns scanning error too:

SELECT a.* FROM [Modules\Engine\Models\Artists\Artists] AS [a] INNER JOIN [Modules\Engine\Models\PerformancesDates ] AS [pd ] ON a.id = pd.owner_id WHERE (a.location = :location:) AND (pd.date = :date:)

    Scanning error before 'Modules\Engine\M...' when parsing: SELECT a.* FROM [Modules\Engine\Models\Artists\Artists] AS [a] INNER JOIN [Modules\Engine\Models\PerformancesDates ] AS [pd ] ON a.id = pd.owner_id WHERE (a.location = :location:) AND (pd.date = :date:) (202)

Here is the method:

     public function explore($location, $date)
    {
        $builder = $this->getDi()->getModelsManager()->createBuilder();

        $builder->columns('a.*')
            ->addFrom('Modules\Engine\Models\Artists\Artists', 'a');

        if (!empty($date) && !empty($location)) {
            $builder->innerJoin('Modules\Engine\Models\PerformancesDates ', 'a.id = pd.owner_id', 'pd ')
                ->andWhere('a.location = :location:', array('location' => $location))
                ->andWhere('pd.date = :date:', array('date' => $date));
        } elseif (!empty($date) && empty($location)) {
            $builder->innerJoin('Modules\Engine\Models\PerformancesDates ', 'a.id = pd.owner_id', 'pd ')
                ->andWhere('pd.date = :date:', array('date' => $date));
        } elseif (!empty($location) && empty($date)) {
            $builder->andWhere('a.location = :location:', array('location' => $location));
        }

        print_r($builder->getPhql());
        die();
    }

Yeah there is a space in your model

'Modules\Engine\Models\PerformancesDates '

vs.

'Modules\Engine\Models\PerformancesDates'

Yeah,works corectly, that was the problem the space :)

Awesome! Glad we figured it out