1.1.9. Direct Queries

If you need to perform queries directly, additional fetch* and yield* methods are provided which expose the underlying Atlas\Pdo\Connection functionality. By using the columns() method, you can select specific columns or individual values. For example:

<?php
// an array of IDs
$threadIds = $atlas
    ->select(Thread::CLASS)
    ->columns(['thread_id'])
    ->limit(10)
    ->orderBy('thread_id DESC')
    ->fetchColumn();

// key-value pairs of IDs and titles
$threadIdsAndTitles = $atlas
    ->select(Thread::CLASS)
    ->columns(['thread_id', 'tite'])
    ->limit(10)
    ->orderBy('thread_id DESC')
    ->fetchKeyPair();

// etc.

See the list of Connection fetch() and yield() methods for more.

You can also call fetchRow() or fetchRows() to get Row objects directly from the Table underlying the Mapper.

1.1.9.1. Complex Queries

You can use any of the direct table access methods with more complex queries and joins as provided by Atlas.Query:

<?php
$threadData = $atlas
    ->select(Thread::CLASS)
    ->columns('threads.subject', 'authors.name', 's.*')
    ->join('INNER', 'authors', 'authors.author_id = threads.author_id')
    ->join('INNER', 'summary AS s', 's.thread_id = threads.thread_id')
    ->where('authors.name = ', $name)
    ->orderBy('threads.thread_id DESC')
    ->offset(2)
    ->limit(2)
    ->fetchUnique();

In addition the various JOIN methods provided by Atlas.Query, the MapperSelect also provides joinWith(), so that you can join on a defined relationship. (The related table will be aliased as the relationship name.) For example, to do an INNER JOIN with another table as defined in the Mapper relationships:

<?php
$threadIdsAndAuthorNames = $atlas
    ->select(Thread::CLASS)
    ->joinWith('INNER', 'author')
    ->columns(
        "thread.thread_id",
        "CONCAT(author.first_name, ' ', author.last_name)"
    )
    ->limit(10)
    ->orderBy('thread_id DESC')
    ->fetchKeyPair();

1.1.9.2. Reusing the Select

The select object can be used for multiple queries, which may be useful for pagination. The generated select statement can also be displayed for debugging purposes.

<?php
$select = $atlas
    ->select(Thread::CLASS)
    ->columns('*')
    ->offset(10)
    ->limit(5);

// Fetch the current result set
$results = $select->fetchAll();

// Fetch the row count without any limit or offset
$totalCount = $select->fetchCount();

// View the generated select statement
$statement = $select->getStatement();