4.1.9. Direct Queries

If you need to perform queries directly, additional fetch* and yield* methods are provided which expose the Extended PDO functionality. By using the $cols parameter, you can select specific columns or individual values. For example:

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

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

// etc.

See the list of ExtendedPdo::fetch*() and yield*() methods for more.

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

4.1.9.1. Fetching

4.1.9.1.1. Fetch Value

Returns a single value, or false.

<?php
$subject = $atlas
    ->select(ThreadMapper::CLASS)
    ->cols(['subject'])
    ->where('thread_id = ?', '1')
    ->fetchValue();

// "Subject One"

4.1.9.1.2. Fetch Column

Returns a sequential array of one column, or an empty array.

<?php
$subjects = $atlas
    ->select(ThreadMapper::CLASS)
    ->cols(['subject'])
    ->limit(2)
    ->fetchCol();

// [
//   0 => "Subject One",
//   1 => "Subject Two"
// ]

4.1.9.1.3. Fetch Pairs

Returns an associative array where the key is the first column and the value is the second column, or an empty array.

<?php
$subjectAndBody = $atlas
    ->select(ThreadMapper::CLASS)
    ->cols(['subject', 'body'])
    ->limit(2)
    ->fetchPairs();

// [
//   'Subject One' => "Body Text One",
//   'Subject Two' => "Body Text Two"
// ]

4.1.9.1.4. Fetch One

Returns an associative array of one row, or false.

<?php
$threadData = $atlas
    ->select(ThreadMapper::CLASS)
    ->cols(['subject', 'body', 'author_id'])
    ->where('thread_id = 1')
    ->fetchOne();

// [
//   'subject' => "Subject One",
//   'body' => "Body Text One",
//   'author_id' => "1"
// ]

4.1.9.1.5. Fetch Assoc

Returns an associative array of rows keyed on the first column specified, or an empty array.

<?php
$threads = $atlas
    ->select(ThreadMapper::CLASS)
    ->cols(['subject', 'body'])
    ->limit(2)
    ->fetchAssoc();

// [
//   'Subject One' => [
//     'subject' => "Subject One",
//     'body' => "Body Text One",
//   ],
//   'Subject Two' => [
//     'subject' => "Subject Two",
//     'body' => "Body Text Two"
//   ]
// ]

4.1.9.1.6. Fetch All

Returns a sequential array of associative arrays, or an empty array.

<?php
$threads = $atlas
    ->select(ThreadMapper::CLASS)
    ->cols(['subject', 'body'])
    ->limit(2)
    ->orderBy('thread_id DESC')
    ->fetchAll();

// [
//   0 => [
//     'subject' => "Subject One",
//     'body' => "Body Text One"
//   ],
//   1 => [
//     'subject' => "Subject Two",
//     'body' => "Body Text Two"
//   ]
// ]

4.1.9.2. Yielding Data

If you prefer to get the results one at a time, you can use the yield* variations on these methods to iterate through the result set instead of returning an array.

4.1.9.2.1. Yield Col

Iterate through a sequential array of one column.

<?php
$subjects = $atlas
    ->select(ThreadMapper::CLASS)
    ->cols(['subject'])
    ->yieldCol();

foreach($subjects as $subject) {
    echo $subject;
}

4.1.9.2.2. Yield Pairs

Iterate through an associative array by the first column specified.

<?php
$subjectAndBody = $atlas
    ->select(ThreadMapper::CLASS)
    ->cols(['subject', 'body'])
    ->yieldPairs();

foreach($subjectAndBody as $subject => $body) {
    echo $subject . ": " . $body;
}

4.1.9.2.3. Yield Assoc

Iterate through an associative array of rows by the first column specified.

<?php
$threads = $atlas
    ->select(ThreadMapper::CLASS)
    ->cols(['thread_id', 'subject'])
    ->yieldAssoc();

foreach($threads as $threadId => $thread) {
    echo $threadId . ": " . $thread['subject'];
}

4.1.9.2.4. Yield All

Iterate through a sequential array of rows.

<?php
$threads = $atlas
    ->select(ThreadMapper::CLASS)
    ->cols(['thread_id', 'subject'])
    ->yieldAll();

 foreach($threads as $thread) {
    echo $thread['thread_id'] . ": " . $thread['subject'];
}

4.1.9.3. Complex Queries

You can use any of the direct table access methods with more complex queries and joins.

<?php
$threadData = $atlas
    ->select(ThreadMapper::CLASS)
    ->cols(['threads.subject', 'authors.name', 's.*'])
    ->join('INNER', 'authors', 'authors.author_id = threads.author_id')
    ->join('INNER', 'summary s', 's.thread_id = threads.thread_id')
    ->where('authors.name = ?', $name)
    ->orderBy('threads.thread_id DESC')
    ->offset(2)
    ->limit(2)
    ->fetchAssoc();

4.1.9.4. 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(ThreadMapper::CLASS)
    ->cols(['*'])
    ->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();