2017 © Pedro Peláez
 

library literate-spoon

Yet another SQL query builder

image

legow/literate-spoon

Yet another SQL query builder

  • Friday, October 13, 2017
  • by adamturcsan
  • Repository
  • 1 Watchers
  • 0 Stars
  • 574 Installations
  • PHP
  • 0 Dependents
  • 0 Suggesters
  • 0 Forks
  • 1 Open issues
  • 20 Versions
  • 15 % Grown

The README.md

Build Status Coverage Status SensioLabsInsight, (*1)

literate-spoon

SQL query builder, (*2)

Usage

<?php

namespace Sample\Data\Model;

use LegoW\LiterateSpoon\Builder;
use LegoW\LiterateSpoon\Component\Direction;
use LegoW\LiterateSpoon\Component\Join;

class News {
    use SomeTrait\With\Database;

    const COL_NEWS_ID = 'news_id';
    const COL_PUBLISH_TIME = 'publish_time';
    const COL_TITLE = 'title';
    const COL_CONTENT = 'publish_time';

    private $columns = [
        self::COL_NEWS_ID,
        self::COL_PUBLISH_TIME,
        self::COL_TITLE,
        self::COL_CONTENT
    ];

    public function getNewsList(int $num, int $offset): array
    {
        $builder = new Builder();
        $select = $builder->select('news', [self::COL_PUBLISH_TIME, self::COL_TITLE]);
        $select->where()
                ->compareColumn('like', self::COL_TITLE, 'paramName')
                ->betweenColumn(self::COL_PUBLISH_TIME, 'param1', 'param2');
        $select->orderBy()
               ->setOrder(self::COL_PUBLISH_TIME, Direction::ASC);
        $select->limit($num, $offset);

        $query = $builder->asString();

        $statement = $this->db->prepare($query);
        $statement->execute([
            'paramName' => '%builder%',
            'param1'    => (new \DateTime('-1 year'))->format('Y-m-d H:i:s'),
            'param2'    => (new \DateTime())->format('Y-m-d H:i:s'),
        ]);
        return $statement->fetchAll();
    }

    public function getNewsWithComments(int $newsId): array
    {
        $builder = new Builder();
        $select = $builder->select('news');
        $select->join('comments', Join::TYPE_LEFT)
            ->using(self::COL_NEWS_ID);
        $select->where()
            ->compareColumn('=', self::COL_NEWS_ID, 'newsId');
        $query = $builder->asString();
        $statement = $this->db->prepare($query);
        $statement->bindValue('newsId', $newsId);
        $statement->execute();
        return $statement->fetch();
    }

    public function add(string $title, string $content): int
    {
        $builder = new Builder();
        $builder->insert('news')
            ->addColumn(self::COL_TITLE)->addValuePlaceHolderFor('title')
            ->addColumn(self::COL_CONTENT)->addValuePlaceHolderFor('content')
            ->addColumn(self::COL_PUBLISH_TIME)->addValuePlaceHolderFor('publishTime');
        $query = $builder->asString();
        $statement = $this->db->prepare($query);
        $statement->bindValue('title', $title);
        $statement->bindValue('content', $content);
        $statement->bindValue('publishTime', (new DateTime())->format('Y-m-d H:i:s'));
        $statement->execute();
        return $this->db->lastInsertId();
    }

    public function update(int $newsId, array $data): bool
    {
        $builder = new Builder();
        $update = $builder->update('news');
        foreach ($this->columns as $columnName) {
            if ($columnName !== self::COL_NEWS_ID && array_key_exists($columnName, $data)) {
                $update->set($columnName, $columnName);
            }
        }
        $update->where()->compareColumn('=', self::COL_NEWS_ID, 'newsId');
        $query = $builder->asString();
        $statement = $this->db->prepare($query);
        foreach ($data as $columnName => $value) {
            if (array_key_exists($columnName, $this->columns)) {
                $statement->bindValue($columnName, $value);
            }
        }
        $statement->bindValue('newsId', $newsId);
        return $statement->execute();
    }
}

The Versions

13/10 2017

dev-develop

dev-develop

Yet another SQL query builder

  Sources   Download

MIT

The Development Requires

by Turcsán Ádám

04/10 2017

dev-master

9999999-dev

Yet another SQL query builder

  Sources   Download

MIT

The Development Requires

by Turcsán Ádám

04/10 2017

0.7.0

0.7.0.0

Yet another SQL query builder

  Sources   Download

MIT

The Development Requires

by Turcsán Ádám

03/10 2017

0.6.2

0.6.2.0

Yet another SQL query builder

  Sources   Download

MIT

The Development Requires

by Turcsán Ádám

03/10 2017

0.6.1

0.6.1.0

Yet another SQL query builder

  Sources   Download

MIT

The Development Requires

by Turcsán Ádám

22/09 2017

0.5.x-dev

0.5.9999999.9999999-dev

Yet another SQL query builder

  Sources   Download

MIT

The Development Requires

by Turcsán Ádám

22/09 2017

0.5.3

0.5.3.0

Yet another SQL query builder

  Sources   Download

MIT

The Development Requires

by Turcsán Ádám

21/09 2017

0.6.0

0.6.0.0

Yet another SQL query builder

  Sources   Download

MIT

The Development Requires

by Turcsán Ádám

13/09 2017

0.5.2

0.5.2.0

Yet another SQL query builder

  Sources   Download

MIT

The Development Requires

by Turcsán Ádám

07/07 2017

0.5.1

0.5.1.0

Yet another SQL query builder

  Sources   Download

MIT

The Development Requires

by Turcsán Ádám

07/07 2017

0.5.0

0.5.0.0

Yet another SQL query builder

  Sources   Download

MIT

The Development Requires

by Turcsán Ádám

29/05 2017

0.4.0

0.4.0.0

Yet another SQL query builder

  Sources   Download

MIT

The Development Requires

by Turcsán Ádám

23/03 2017

0.3.2

0.3.2.0

Yet another SQL query builder

  Sources   Download

MIT

The Development Requires

by Turcsán Ádám

22/03 2017

0.3.1

0.3.1.0

Yet another SQL query builder

  Sources   Download

MIT

The Development Requires

by Turcsán Ádám

22/03 2017

0.3.0

0.3.0.0

Yet another SQL query builder

  Sources   Download

MIT

The Development Requires

by Turcsán Ádám

22/03 2017

0.2.2

0.2.2.0

Yet another SQL query builder

  Sources   Download

MIT

The Development Requires

by Turcsán Ádám

22/03 2017

0.2.1

0.2.1.0

Yet another SQL query builder

  Sources   Download

MIT

The Development Requires

by Turcsán Ádám

21/03 2017

0.2.0

0.2.0.0

Yet another SQL query builder

  Sources   Download

MIT

The Development Requires

by Turcsán Ádám

29/12 2016

0.1.1

0.1.1.0

Yet another SQL query builder

  Sources   Download

MIT

The Development Requires

by Turcsán Ádám

02/12 2016

0.1.0

0.1.0.0

Yet another SQL query builder

  Sources   Download

MIT

The Development Requires

by Turcsán Ádám